Two Ways To Rollup

In Three Powerful SQL Words, I showed how to enhance summary queries with aggregate values over subsets of grouping columns. Today I continue that discussion by showing the two syntaxes of rollup and illustrating the difference between them.

First, assume a table of accounting transactions.

select department, account, amount
  from xacts2
 order by 1, 2
 
DEPARTMENT  ACCOUNT     AMOUNT
     1          10      200.00
     1          10      250.00
     1          30      300.00
     2          10      125.00
     2          20      175.00
     2          20      225.00

Here’s a typical summary query using the familiar GROUP BY clause.

select department, account, sum(amount) as tamt
  from xacts2                                  
 group by department, account                  
 order by 1,2                                  

DEPARTMENT  ACCOUNT       TAMT
     1          10      450.00
     1          30      300.00
     2          10      125.00
     2          20      400.00

The amount column is summarized by two columns–department and account.

To add additional aggregate totals is an easy matter of adding WITH ROLLUP.

select department, account, sum(amount) as tamt
  from xacts2                                  
 group by department, account                  
  with rollup 
 order by 1,2  
 
DEPARTMENT  ACCOUNT         TAMT
     1          10        450.00
     1          30        300.00
     1            -       750.00
     2          10        125.00
     2          20        400.00
     2            -       525.00
      -           -     1,275.00

This gives me department totals and grand totals.

Here’s a different form of rollup that does the same thing.

select department, account, sum(amount) as tamt
  from xacts2                                  
 group by rollup(department, account)          
 order by 1,2                                  

DEPARTMENT  ACCOUNT        TAMT
     1          10       450.00
     1          30       300.00
     1            -      750.00
     2          10       125.00
     2          20       400.00
     2            -      525.00
      -           -    1,275.00

So is one form as good as another? Consider the following queries.

select department, account, sum(amount) as tamt
  from xacts2                                  
 group by rollup(department),account           
 order by 1,2                                  

DEPARTMENT  ACCOUNT       TAMT
     1          10      450.00
     1          30      300.00
     2          10      125.00
     2          20      400.00
      -         10      575.00
      -         20      400.00
      -         30      300.00

Notice that there are aggregates for account, but not for department.

Here’s another variation.

select department, account, sum(amount) as tamt
  from xacts2                                  
 group by department ,rollup(account)          
 order by 1,2                                  

DEPARTMENT  ACCOUNT        TAMT
     1          10       450.00
     1          30       300.00
     1            -      750.00
     2          10       125.00
     2          20       400.00
     2            -      525.00

Here we have aggregate figures for department, but not for account.

Consider one final example.

select department, account, sum(amount) as tamt
  from xacts2                                  
 group by rollup(department) ,rollup(account)  
 order by 1,2                                  
	  
DEPARTMENT  ACCOUNT        TAMT
     1          10       450.00
     1          30       300.00
     1            -      750.00
     2          10       125.00
     2          20       400.00
     2            -      525.00
      -         10       575.00
      -         20       400.00
      -         30       300.00
      -           -    1,275.00

We have totals by department, totals by account, and grand totals. This is equivalent to WITH CUBE.

Which form is better–GROUP BY ROLLUP or WITH ROLLUP? Probably GROUP BY ROLLUP, for two reasons:

1. From what I can tell, GROUP BY ROLLUP seems to be more standard and widespread throughout the relational database world than WITH ROLLUP.

2. The GROUP BY ROLLUP syntax allows us to produce result sets that we can’t get with WITH ROLLUP.

For these reasons, I plan to devote more time to mastering the nuances of the GROUP BY ROLLUP syntax.

RELATED STORIES

Three Powerful SQL Words

Treasury of new DB2 6.1 Features, Part 2: Grouping Sets and Super Groups

IBM Knowledge Center