August 1, 2014 at 5:49 am
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 Attempt2 Attempts3 Attempts4 Or MoreTotal
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
August 3, 2014 at 4:54 am
Quick question, can you supply the table structure and sample data in a consumable format?
😎
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply