Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Pivotting on 2 Totals Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, August 1, 2014 5:48 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, November 28, 2016 7:09 AM Points: 109, Visits: 577
 Hi guysI'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] ,TotalFROM( SELECT CONVERT(DATE,[Date]) Date ,ISNULL(AttemptsFlag,'Total') as Attempt ,COUNT(NoOfTimes) AS Totals FROM XXXXX GROUP BY CUBE([Date],AttemptsFlag))aPIVOT( SUM(Totals) FOR Attempt IN ([1 Attempt],[2 Attempts],[3 Attempts],[4 Or More], [Total])) AS pvtBasically 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 Total2012-09-04 239 68 2 8 317The 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 8If 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 groupingsCan 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
 SSCertifiable Group: General Forum Members Last Login: Today @ 1:59 PM Points: 6,573, Visits: 17,293
 Quick question, can you supply the table structure and sample data in a consumable format?
Post #1599017

 Permissions