Grand Total using ROLLUP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Grand Total using ROLLUP

Author

Topic

duncanwill
Starting Member

20 Posts

Posted - 2011-03-02 : 05:10:22

Hi,

I have a query where I require a grand total row at the foot of my recordset. I can use WITH ROLLUP, but my query has multiple GROUP BY columns and so I have a result set where totals are produced for every grouping combination - all I want is the final Total row not the individual group total rows within the data.

Is this possible with ROLLUP on SQL 2005 - or should I get the final row in a UNION or similar?

Cheers

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-03-02 : 07:55:43

Try something like this:


SELECT col1, col2, col3, SUM(col4) AS total
  FROM (SELECT 1, 'a', 'x', 10
         UNION ALL
        SELECT 1, 'a', 'x', 20
         UNION ALL
        SELECT 1, 'a', 'y', 30
         UNION ALL
        SELECT 1, 'b', 'x', 100
         UNION ALL
        SELECT 2, 'a', 'x', 1000) AS S(col1, col2, col3, col4)
 GROUP BY col1, col2, col3 WITH ROLLUP
HAVING GROUPING(col1) = GROUPING(col2)
   AND GROUPING(col2) = GROUPING(col3);

By the way in 2008 and beyond this becomes much easier using GROUPING SETS like this:


SELECT col1, col2, col3, SUM(col4) AS total
  FROM (SELECT 1, 'a', 'x', 10
         UNION ALL
        SELECT 1, 'a', 'x', 20
         UNION ALL
        SELECT 1, 'a', 'y', 30
         UNION ALL
        SELECT 1, 'b', 'x', 100
         UNION ALL
        SELECT 2, 'a', 'x', 1000) AS S(col1, col2, col3, col4)
 GROUP BY GROUPING SETS((col1, col2, col3), ())

duncanwill
Starting Member

20 Posts

Posted - 2011-03-02 : 09:11:17

That's a thing of beauty and does the job a treat! 'Roll on' SQL 2008!

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources