Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pivotting on 2 Totals Expand / Collapse
Author
Message
Posted Friday, August 1, 2014 5:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:26 AM
Points: 60, Visits: 345
Hi guys

I'm trying to get my head around using the GROUP BY CUBE aggregation. Currently I have this working as such:
SELECT
ISNULL(CONVERT(VARCHAR,Date), 'Grand Total') Date
,ISNULL([1 Attempt],0) [1 Attempt]
,ISNULL([2 Attempts],0) AS [2 Attempts]
,ISNULL([3 Attempts],0) AS [3 Attempts]
,ISNULL([4 Or More],0) AS [4 Or More]
,Total
FROM
(
SELECT
CONVERT(DATE,[Date]) Date
,ISNULL(AttemptsFlag,'Total') as Attempt
,COUNT(NoOfTimes) AS Totals
FROM
XXXXX
GROUP BY
CUBE([Date],AttemptsFlag)
)a
PIVOT
(
SUM(Totals)
FOR Attempt IN ([1 Attempt],[2 Attempts],[3 Attempts],[4 Or More], [Total])
) AS pvt

Basically this is used to work similar to a Pivot table in excel. My data will look as follows:

Date 1 Attempt 2 Attempts 3 Attempts 4 Or More Total
2012-09-04 239 68 2 8 317

The problem I'm having is the Total column. Although this is summing the line values correctly, the total should be based on the sum not count of attempts i.e. 1 x 239, 2 x 68, 3 x 2, 4 x 8

If I change the FROM select clause to use SUM instead of COUNT
SELECT
CONVERT(DATE,[Date]) Date
,ISNULL(AttemptsFlag,'Total') as Attempt
,SUM(NoOfTimes) AS Totals
FROM
XXXXX
GROUP BY
CUBE([Date],AttemptsFlag)
it will return the correct Total amount but not the right numbers for the Attempt groupings

Can anyone advise a method to do this in a clean way? I don't want to go down the road of dumping 2 different queries into temp tables and joining on date etc
Post #1598651
Posted Sunday, August 3, 2014 4:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:43 AM
Points: 2,419, Visits: 6,713
Quick question, can you supply the table structure and sample data in a consumable format?
Post #1599017
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse