Recipe12.14.Identifying Rows That Are Not Subtotals

Recipe 12.14. Identifying Rows That Are Not Subtotals

Problem

You've used the CUBE extension of the GROUP BY clause to create a report, and you need a way to differentiate between rows that would be generated by a normal GROUP BY clause and those rows that have been generated as a result of using CUBE or ROLLUP.

Following is the result set from a query using the CUBE extension to GROUP BY to create a breakdown of the salaries in table EMP:

 DEPTNO JOB           SAL ------ --------- -------                    29025        CLERK        4150        ANALYST      6000        MANAGER      8275        SALESMAN     5600        PRESIDENT    5000     10              8750     10 CLERK        1300     10 MANAGER      2450     10 PRESIDENT    5000     20             10875     20 CLERK        1900     20 ANALYST      6000     20 MANAGER      2975     30              9400     30 CLERK         950     30 MANAGER      2850     30 SALESMAN     5600 

This report includes the sum of all salaries by DEPTNO and JOB (for each JOB per DEPTNO), the sum of all salaries by DEPTNO, the sum of all salaries by JOB, and finally a grand total (the sum of all salaries in table EMP). You want to clearly identify the different levels of aggregation. You want to be able to identify which category an aggregated value belongs to (i.e., does a given value in the SAL column represent a total by DEPTNO? By JOB? The grand total?). You would like to return the following result set:

 DEPTNO JOB           SAL DEPTNO_SUBTOTALS JOB_SUBTOTALS ------ --------- ------- ---------------- -------------                    29025                1             1        CLERK        4150                1             0        ANALYST      6000                1             0        MANAGER      8275                1             0        SALESMAN     5600                1             0        PRESIDENT    5000                1             0     10              8750                0             1     10 CLERK        1300                0             0     10 MANAGER      2450                0             0     10 PRESIDENT    5000                0             0     20             10875                0             1     20 CLERK        1900                0             0     20 ANALYST      6000                0             0     20 MANAGER      2975                0             0     30              9400                0             1     30 CLERK         950                0             0     30 MANAGER      2850                0             0     30 SALESMAN     5600                0             0 

Solution

Use the GROUPING function to identify which values exist due to CUBE's or ROLLUP's creation of subtotals, or superaggregate values. The following is an example for DB2 and Oracle:

  1 select deptno, job, sum(sal) sal,  2        grouping(deptno) deptno_subtotals,  3        grouping(job) job_subtotals  4   from emp  5  group by cube(deptno,job) 

The only difference between the SQL Server solution and that for DB2 and Oracle lies in how the CUBE/ROLLUP clauses are written:

  1 select deptno, job, sum(sal) sal,  2        grouping(deptno) deptno_subtotals,  3        grouping(job) job_subtotals  4   from emp  5  group by deptno,job with cube 

This recipe is meant to highlight the use of CUBE and GROUPING when working with subtotals. As of the time of this writing, PostgreSQL and MySQL support neither CUBE nor GROUPING.

Discussion

If DEPTNO_SUBTOTALS is 1, then the value in SAL represents a subtotal by DEPTNO created by CUBE. If JOB_SUBTOTALS is 1, then the value in SAL represents a subtotal by JOB created by CUBE. If both JOB_SUBTOTALS and DEPTNO_ SUBTOTALS are 1, then the value in SAL represents a grand total of all salaries created by CUBE. Rows with 0 for both DEPTNO_SUBTOTALS and JOB_SUBTOTALS represent rows created by regular aggregation (the sum of SAL for each DEPTNO/JOB combination).