Recipe12.20.Pivoting a Result Set with Subtotals

Recipe 12.20. Pivoting a Result Set with Subtotals

Problem

You want to create a report containing subtotals, then transpose the results to provide a more readable report. For example, you've been asked to create a report that displays for each department, the managers in the department along with a sum of the salaries of the employees who work for those managers. Additionally, you want to return two subtotals: the sum of all salaries in each department for those employees who have managers, and a sum of all salaries in the result set (the sum of the department subtotals). You currently have the following report:

 DEPTNO        MGR        SAL ------ ---------- ----------     10       7782       1300     10       7839       2450     10                  3750     20       7566       6000     20       7788       1100     20       7839       2975     20       7902        800     20                 10875     30       7698       6550     30       7839       2850     30                  9400                        24025 

You want to provide a more readable report and wish to transform the above result set to the following, which makes the meaning of the report much more clear:

 MGR     DEPT10     DEPT20     DEPT30      TOTAL ---- ---------- ---------- ---------- ---------- 7566          0       6000          0 7698          0          0       6550 7782       1300          0          0 7788          0       1100          0 7839       2450       2975       2850 7902          0        800          0            3750      10875       9400     24025 

Solution

The first step is to generate subtotals using the ROLLUP extension to GROUP BY. The next step is to perform a classic pivot (aggregate and CASE expression) to create the desired columns for your report. The GROUPING function allows you to easily determine which values are subtotals (that is, exist because of ROLLUP and otherwise would not normally be there). Depending on how your RDBMS sorts NULL values, you may need to add an ORDER BY to the solution to allow it to look like the target result set above.

DB2 and Oracle

Use the ROLLUP extension to GROUP BY then use a CASE expression to format the data into a more readable report:

  1 select mgr,  2        sum(case deptno when 10 then sal else 0 end) dept10,  3        sum(case deptno when 20 then sal else 0 end) dept20,  4        sum(case deptno when 30 then sal else 0 end) dept30,  5        sum(case flag when '11' then sal else null end) total  6   from (  7 select deptno,mgr,sum(sal) sal,  8        cast(grouping(deptno) as char(1))||  9        cast(grouping(mgr) as char(1)) flag 10   from emp 11  where mgr is not null 12  group by rollup(deptno,mgr) 13        ) x 14  group by mgr 

SQL Server

Use the ROLLUP extension to GROUP BY then use a CASE expression to format the data into a more readable report:

  1 select mgr,  2        sum(case deptno when 10 then sal else 0 end) dept10,  3        sum(case deptno when 20 then sal else 0 end) dept20,  4        sum(case deptno when 30 then sal else 0 end) dept30,  5        sum(case flag when '11' then sal else null end) total  6   from (  7 select deptno,mgr,sum(sal) sal,  8        cast(grouping(deptno) as char(1))+  9        cast(grouping(mgr) as char(1)) flag 10   from emp 11  where mgr is not null 12  group by deptno,mgr with rollup 13        ) x 14  group by mgr 

MySQL and PostgreSQL

The GROUPING function is not supported by either RDBMS.

Discussion

The solutions provided above are identical except for the string concatenation and how GROUPING is specified. Because the solutions are so similar, the discussion below will refer to the SQL Server solution to highlight the intermediate result sets (the discussion is relevant to DB2 and Oracle as well).

The first step is to generate a result set that sums the SAL for the employees in each DEPTNO per MGR. The idea is to show how much the employees make under a particular manager in a particular department. For example, this query below will allow you to compare the salaries of employees who work for KING in DEPTNO 10 compared with those who work for KING in DEPTNO 30:

 select deptno,mgr,sum(sal) sal   from emp  where mgr is not null  group by mgr,deptno  order by 1,2 DEPTNO        MGR        SAL ------ ---------- ----------     10       7782       1300     10       7839       2450     20       7566       6000     20       7788       1100     20       7839       2975     20       7902        800     30       7698       6550     30       7839       2850 

The next step is to use the ROLLUP extension to GROUP BY to create subtotals for each DEPTNO and across all employees (who have a manager):

 select deptno,mgr,sum(sal) sal   from emp  where mgr is not null  group by deptno,mgr with rollup DEPTNO        MGR        SAL ------ ---------- ----------     10       7782       1300     10       7839       2450     10                  3750     20       7566       6000     20       7788       1100     20       7839       2975     20       7902        800     20                 10875     30       7698       6550     30       7839       2850     30                  9400                        24025 

With the subtotals created, you need a way to determine which values are in fact subtotals (created by ROLLUP) and which are results of the regular GROUP BY. Use the GROUPING function to create bitmaps to help identify the subtotal values from the regular aggregate values:

 select deptno,mgr,sum(sal) sal,        cast(grouping(deptno) as char(1))+        cast(grouping(mgr) as char(1)) flag   from emp  where mgr is not null  group by deptno,mgr with rollup DEPTNO        MGR        SAL FLAG ------ ---------- ---------- ----     10       7782       1300 00     10       7839       2450 00     10                  3750 01     20       7566       6000 00     20       7788       1100 00     20       7839       2975 00     20       7902        800 00     20                 10875 01     30       7698       6550 00     30       7839       2850 00     30                  9400 01                        24025 11 

If it isn't immediately obvious, the rows with a value of 00 for FLAG are the results of regular aggregation. The rows with a value of 01 for FLAG are the results of ROLLUP aggregating SAL by DEPTNO (since DEPTNO is listed first in the ROLLUP; if you switch the order, for example, "GROUP BY MGR, DEPTNO WITH ROLLUP", you'd see quite different results). The row with a value of 11 for FLAG is the result of ROLLUP aggregating SAL over all rows.

At this point you have everything you need to create a beautified report by simply using CASE expressions. The goal is to provide a report that shows employee salaries for each manager across departments. If a manager does not have any subordinates in a particular department, a zero should be returned; otherwise, you want to return the sum of all salaries for that manager's subordinates in that department. Additionally, you want to add a final column, TOTAL, representing a sum of all the salaries in the report. The solution satisfying all these requirements is shown below:

 select mgr,        sum(case deptno when 10 then sal else 0 end) dept10,        sum(case deptno when 20 then sal else 0 end) dept20,        sum(case deptno when 30 then sal else 0 end) dept30,        sum(case flag when '11' then sal else null end) total   from ( select deptno,mgr,sum(sal) sal,        cast(grouping(deptno) as char(1))+        cast(grouping(mgr) as char(1)) flag   from emp  where mgr is not null  group by deptno,mgr with rollup        ) x  group by mgr  order by coalesce(mgr,9999) MGR     DEPT10     DEPT20     DEPT30     TOTAL ---- ---------- ---------- ---------- ---------- 7566          0       6000          0 7698          0          0       6550 7782       1300          0          0 7788          0       1100          0 7839       2450       2975       2850 7902          0        800          0            3750      10875       9400    24025