When using the GROUP BY clause in SQL Server, we can use the ROLLUP modifier to create subtotals and grand totals, etc.

You may have seen two variations of this. One as GROUP BY ROLLUP () and the other as GROUP BY ... WITH ROLLUP.

You may be wondering which one you should use?

As it turns out, Microsoft recommends that we use the first syntax; GROUP BY ROLLUP (). The other syntax is provided for backward compatibility only.

Recommended Syntax

Here’s an example that uses the recommended syntax:

SELECT 
    Activity, 
    SUM(Score) AS "Score" 
FROM Dogs  
GROUP BY ROLLUP (Activity);

Result:

Activity     Score
-----------  -----
Fetch Stick  177  
Keep Quiet   4    
Wag Tail     203  
null         384  

This the recommended syntax, which goes GROUP BY ROLLUP () with any column names enclosed in the parentheses.

Multiple column names can be included, as long as they’re separated by a comma:

SELECT 
    Activity, 
    DogName,
    SUM(Score) AS "Score"
FROM Dogs  
GROUP BY ROLLUP (Activity, DogName);

Result:

Activity     DogName  Score
-----------  -------  -----
Fetch Stick  Bruno    43   
Fetch Stick  Cooper   43   
Fetch Stick  Max      91   
Fetch Stick  null     177  
Keep Quiet   Bruno    3    
Keep Quiet   Cooper   null 
Keep Quiet   Max      1    
Keep Quiet   null     4    
Wag Tail     Bruno    65   
Wag Tail     Cooper   51   
Wag Tail     Max      87   
Wag Tail     null     203  
null         null     384  

Old Syntax

The following example rewrites the previous example using the old syntax:

SELECT 
    Activity, 
    DogName,
    SUM(Score) AS "Score"
FROM Dogs  
GROUP BY Activity, DogName WITH ROLLUP;

Result:

Activity     DogName  Score
-----------  -------  -----
Fetch Stick  Bruno    43   
Fetch Stick  Cooper   43   
Fetch Stick  Max      91   
Fetch Stick  null     177  
Keep Quiet   Bruno    3    
Keep Quiet   Cooper   null 
Keep Quiet   Max      1    
Keep Quiet   null     4    
Wag Tail     Bruno    65   
Wag Tail     Cooper   51   
Wag Tail     Max      87   
Wag Tail     null     203  
null         null     384  

This syntax is provided for backward compatibility only.

It’s the same with the CUBE keyword. It should be used as GROUP BY CUBE(...) rather than GROUP BY ... WITH CUBE.