## Pivotting on 2 Totals

 Author Message mitzyturbo SSC-Enthusiastic Group: General Forum Members 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 Eirikur Eiriksson SSCertifiable Group: General Forum Members Points: 6691 Visits: 17682 Quick question, can you supply the table structure and sample data in a consumable format?