Recipe12.12.Calculating Simple Subtotals

Recipe 12.12. Calculating Simple Subtotals

Problem

For the purposes of this recipe, a "simple subtotal" is defined as a result set that contains values from the aggregation of one column along with a grand total value for the table. An example would be a result set that sums the salaries in table EMP by JOB, and that also includes the sum of all salaries in table EMP. The summed salaries by JOB are the subtotals, and the sum of all salaries in table EMP is the grand total. Such a result set should look as follows:

 JOB              SAL --------- ---------- ANALYST         6000 CLERK           4150 MANAGER         8275 PRESIDENT       5000 SALESMAN        5600 TOTAL          29025 

Solution

The ROLLUP extension to the GROUP BY clause solves this problem perfectly. If ROLLUP is not available for your RDBMS, you can solve the problem, albeit with more difficulty, using a scalar subquery or a UNION query.

DB2 and Oracle

Use the aggregate function SUM to sum the salaries, and use the ROLLUP extension of GROUP BY to organize the results into subtotals (by JOB) and a grand total (for the whole table):

 1 select case grouping(job) 2             when 0 then job 3             else 'TOTAL' 4        end job, 5        sum(sal) sal 6   from emp 7  group by rollup(job) 

SQL Server and MySQL

Use the aggregate function SUM to sum the salaries, and use WITH ROLLUP to organize the results into subtotals (by JOB) and a grand total (for the whole table). Then use COALESCE to supply the label 'TOTAL' for the grand total row (which will otherwise have a NULL in the job column):

 1 select coalesce(job,'TOTAL') job, 2        sum(sal) sal 3   from emp 4  group by job with rollup 

With SQL Server, you also have the option to use the GROUPING function shown in the Oracle/DB2 recipe rather than COALESCE to determine the level of aggregation.

PostgreSQL

Use the aggregate function SUM to sum the salaries by DEPTNO. Then UNION ALL with a query generating the sum of all the salaries in the table:

 1 select job, sum(sal) as sal 2   from emp 3  group by job 4  union all 5 select 'TOTAL', sum(sal) 6   from emp 

Discussion

DB2 and Oracle

The first step is to use the aggregate function SUM, grouping by JOB in order to sum the salaries by JOB:

  select job, sum(sal) sal   from emp  group by job JOB         SAL --------- ----- ANALYST    6000 CLERK      4150 MANAGER    8275 PRESIDENT  5000 SALESMAN   5600 

The next step is to use the ROLLUP extension to GROUP BY to produce a grand total for all salaries along with the subtotals for each JOB:

  select job, sum(sal) sal   from emp  group by rollup(job) JOB           SAL --------- ------- ANALYST      6000 CLERK        4150 MANAGER      8275 PRESIDENT    5000 SALESMAN     5600             29025 

The last step is to use the GROUPING function in the JOB column to display a label for the grand total. If the value of JOB is NULL, the GROUPING function will return 1, which signifies that the value for SAL is the grand total created by ROLLUP. If the value of JOB is not NULL, the GROUPING function will return 0, which signifies the value for SAL is the result of the GROUP BY, not the ROLLUP. Wrap the call to GROUPING(JOB) in a CASE expression that returns either the job name or the label 'TOTAL', as appropriate:

  select case grouping(job)             when 0 then job             else 'TOTAL'        end job,        sum(sal) sal   from emp  group by rollup(job) JOB              SAL --------- ---------- ANALYST         6000 CLERK           4150 MANAGER         8275 PRESIDENT       5000 SALESMAN        5600 TOTAL          29025 

SQL Server and MySQL

The first step is to use the aggregate function SUM, grouping the results by JOB to generate salary sums by JOB:

  select job, sum(sal) sal   from emp  group by job JOB         SAL --------- ----- ANALYST    6000 CLERK      4150 MANAGER    8275 PRESIDENT  5000 SALESMAN   5600 

The next step is to use GROUP BY's ROLLUP extension to produce a grand total for all salaries along with the subtotals for each JOB:

  select job, sum(sal) sal   from emp  group by job with rollup JOB           SAL --------- ------- ANALYST      6000 CLERK        4150 MANAGER      8275 PRESIDENT    5000 SALESMAN     5600             29025 

The last step is to use the COEALESCE function against the JOB column. If the value of JOB is NULL, the value for SAL is the grand total created by ROLLUP. If the value of JOB is not NULL, the value for SAL is the result of the "regular" GROUP BY, not the ROLLUP:

  select coalesce(job,'TOTAL') job,        sum(sal) sal   from emp  group by job with rollup JOB              SAL --------- ---------- ANALYST         6000 CLERK           4150 MANAGER         8275 PRESIDENT       5000 SALESMAN        5600 TOTAL          29025 

PostgreSQL

The first step is to group the results by job, using the aggregate function SUM to return salary totals by JOB:

  select job, sum(sal) sal   from emp  group by job JOB         SAL --------- ----- ANALYST    6000 CLERK      4150 MANAGER    8275 PRESIDENT  5000 SALESMAN   5600 

The last step is to use a UNION ALL to supply the grand total to the above query:

  select job, sum(sal) as sal   from emp  group by job  union all select 'TOTAL', sum(sal)   from emp JOB           SAL --------- ------- ANALYST      6000 CLERK        4150 MANAGER      8275 PRESIDENT    5000 SALESMAN     5600 TOTAL       29025