Recipe12.13.Calculating Subtotals for All Possible Expression Combinations
Recipe 12.13. Calculating Subtotals for All Possible Expression CombinationsProblemYou want to find the sum of all salaries by DEPTNO, and by JOB, for every JOB/ DEPTNO combination. You also want a grand total for all salaries in table EMP. You want to return the following result set: DEPTNO JOB CATEGORY SAL ------ --------- --------------------- ------- 10 CLERK TOTAL BY DEPT AND JOB 1300 10 MANAGER TOTAL BY DEPT AND JOB 2450 10 PRESIDENT TOTAL BY DEPT AND JOB 5000 20 CLERK TOTAL BY DEPT AND JOB 1900 30 CLERK TOTAL BY DEPT AND JOB 950 30 SALESMAN TOTAL BY DEPT AND JOB 5600 30 MANAGER TOTAL BY DEPT AND JOB 2850 20 MANAGER TOTAL BY DEPT AND JOB 2975 20 ANALYST TOTAL BY DEPT AND JOB 6000 CLERK TOTAL BY JOB 4150 ANALYST TOTAL BY JOB 6000 MANAGER TOTAL BY JOB 8275 PRESIDENT TOTAL BY JOB 5000 SALESMAN TOTAL BY JOB 5600 10 TOTAL BY DEPT 8750 30 TOTAL BY DEPT 9400 20 TOTAL BY DEPT 10875 GRAND TOTAL FOR TABLE 29025 SolutionExtensions added to GROUP BY in recent years make this a fairly easy problem to solve. If your platform does not supply such extensions for computing various levels of subtotals, then you must compute them manually (via self joins or scalar subqueries). DB2For DB2, you will need to CAST the results from GROUPING to the CHAR(1) data type: 1 select deptno, 2 job, 3 case cast(grouping(deptno) as char(1))|| 4 cast(grouping(job) as char(1)) 5 when '00' then 'TOTAL BY DEPT AND JOB' 6 when '10' then 'TOTAL BY JOB' 7 when '01' then 'TOTAL BY DEPT' 8 when '11' then 'TOTAL FOR TABLE' 9 end category, 10 sum(sal) 11 from emp 12 group by cube(deptno,job) 13 order by grouping(job),grouping(deptno) OracleUse the CUBE extension to the GROUP BY clause with the concatenation operator ||: 1 select deptno, 2 job, 3 case grouping(deptno)||grouping(job) 4 when '00' then 'TOTAL BY DEPT AND JOB' 5 when '10' then 'TOTAL BY JOB' 6 when '01' then 'TOTAL BY DEPT' 7 when '11' then 'GRAND TOTAL FOR TABLE' 8 end category, 9 sum(sal) sal 10 from emp 11 group by cube(deptno,job) 12 order by grouping(job),grouping(deptno) SQL ServerUse the CUBE extension to the GROUP BY clause. For SQL Server, you will need to CAST the results from GROUPING to CHAR(1), and you will need to use the + operator for concatenation (as opposed to Oracle's || operator): 1 select deptno, 2 job, 3 case cast(grouping(deptno)as char(1))+ 4 cast(grouping(job)as char(1)) 5 when '00' then 'TOTAL BY DEPT AND JOB' 6 when '10' then 'TOTAL BY JOB' 7 when '01' then 'TOTAL BY DEPT' 8 when '11' then 'GRAND TOTAL FOR TABLE' 9 end category, 10 sum(sal) sal 11 from emp 12 group by deptno,job with cube 13 order by grouping(job),grouping(deptno) PostgreSQL and MySQLUse multiple UNION ALLs, creating different sums for each: 1 select deptno, job, 2 'TOTAL BY DEPT AND JOB' as category, 3 sum(sal) as sal 4 from emp 5 group by deptno, job 6 union all 7 select null, job, 'TOTAL BY JOB', sum(sal) 8 from emp 9 group by job 10 union all 11 select deptno, null, 'TOTAL BY DEPT', sum(sal) 12 from emp 13 group by deptno 14 union all 15 select null,null,'GRAND TOTAL FOR TABLE', sum(sal) 16 from emp DiscussionOracle, DB2, and SQL ServerThe solutions for all three are essentially the same. The first step is to use the aggregate function SUM and group by both DEPTNO and JOB to find the total salaries for each JOB and DEPTNO combination: select deptno, job, sum(sal) sal from emp group by deptno, job DEPTNO JOB SAL ------ --------- ------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 CLERK 1900 20 ANALYST 6000 20 MANAGER 2975 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 The next step is to create subtotals by JOB and DEPTNO along with the grand total for the whole table. Use the CUBE extension to the GROUP BY clause to perform aggregations on SAL by DEPTNO, JOB, and for the whole table: select deptno, job, sum(sal) sal from emp group by cube(deptno,job) 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 Next, use the GROUPING function in conjunction with CASE to format the results into more meaningful output. The value from GROUPING(JOB) will be 1 or 0 depending on whether or not the values for SAL are due to the GROUP BY or the CUBE. If the results are due to the CUBE, the value will be 1, otherwise it will be 0. The same goes for GROUPING(DEPTNO). Looking at the first step of the solution, you should see that grouping is done by DEPTNO and JOB. Thus, the expected values from the calls to GROUPING when a row represents a combination of both DEPTNO and JOB is 0. The query below confirms this: select deptno, job, grouping(deptno) is_deptno_subtotal, grouping(job) is_job_subtotal, sum(sal) sal from emp group by cube(deptno,job) order by 3,4 DEPTNO JOB IS_DEPTNO_SUBTOTAL IS_JOB_SUBTOTAL SAL ------ --------- ------------------ --------------- ------- 10 CLERK 0 0 1300 10 MANAGER 0 0 2450 10 PRESIDENT 0 0 5000 20 CLERK 0 0 1900 30 CLERK 0 0 950 30 SALESMAN 0 0 5600 30 MANAGER 0 0 2850 20 MANAGER 0 0 2975 20 ANALYST 0 0 6000 10 0 1 8750 20 0 1 10875 30 0 1 9400 CLERK 1 0 4150 ANALYST 1 0 6000 MANAGER 1 0 8275 PRESIDENT 1 0 5000 SALESMAN 1 0 5600 1 1 29025 The final step is to use a CASE expression to determine which category each row belongs to based on the values returned by GROUPING(JOB) and GROUPING(DEPTNO) concatenated: select deptno, job, case grouping(deptno)||grouping(job) when '00' then 'TOTAL BY DEPT AND JOB' when '10' then 'TOTAL BY JOB' when '01' then 'TOTAL BY DEPT' when '11' then 'GRAND TOTAL FOR TABLE' end category, sum(sal) sal from emp group by cube(deptno,job) order by grouping(job),grouping(deptno) DEPTNO JOB CATEGORY SAL ------ --------- --------------------- ------- 10 CLERK TOTAL BY DEPT AND JOB 1300 10 MANAGER TOTAL BY DEPT AND JOB 2450 10 PRESIDENT TOTAL BY DEPT AND JOB 5000 20 CLERK TOTAL BY DEPT AND JOB 1900 30 CLERK TOTAL BY DEPT AND JOB 950 30 SALESMAN TOTAL BY DEPT AND JOB 5600 30 MANAGER TOTAL BY DEPT AND JOB 2850 20 MANAGER TOTAL BY DEPT AND JOB 2975 20 ANALYST TOTAL BY DEPT AND JOB 6000 CLERK TOTAL BY JOB 4150 ANALYST TOTAL BY JOB 6000 MANAGER TOTAL BY JOB 8275 PRESIDENT TOTAL BY JOB 5000 SALESMAN TOTAL BY JOB 5600 10 TOTAL BY DEPT 8750 30 TOTAL BY DEPT 9400 20 TOTAL BY DEPT 10875 GRAND TOTAL FOR TABLE 29025 This Oracle solution implicitly converts the results from the GROUPING functions to a character type in preparation for concatenating the two values. DB2 and SQL Server users will need to explicitly CAST the results of the GROUPING functions to CHAR(1) as shown in the solution. In addition, SQL Server users must use the + operator, and not the || operator, to concatenate the results from the two GROUPING calls into one string. For Oracle and DB2 users, there is an additional extension to GROUP BY called GROUPING SETS; this extension is extremely useful. For example, you can use GROUPING SETS to mimic the output created by CUBE as is done below (DB2 and SQL Server users will need to add explicit CASTS to the values returned by the GROUPING function just as in the CUBE solution): select deptno, job, case grouping(deptno)||grouping(job) when '00' then 'TOTAL BY DEPT AND JOB' when '10' then 'TOTAL BY JOB' when '01' then 'TOTAL BY DEPT' when '11' then 'GRAND TOTAL FOR TABLE' end category, sum(sal) sal from emp group by grouping sets ((deptno),(job),(deptno,job),( )) DEPTNO JOB CATEGORY SAL ------ --------- --------------------- ------- 10 CLERK TOTAL BY DEPT AND JOB 1300 20 CLERK TOTAL BY DEPT AND JOB 1900 30 CLERK TOTAL BY DEPT AND JOB 950 20 ANALYST TOTAL BY DEPT AND JOB 6000 10 MANAGER TOTAL BY DEPT AND JOB 2450 20 MANAGER TOTAL BY DEPT AND JOB 2975 30 MANAGER TOTAL BY DEPT AND JOB 2850 30 SALESMAN TOTAL BY DEPT AND JOB 5600 10 PRESIDENT TOTAL BY DEPT AND JOB 5000 CLERK TOTAL BY JOB 4150 ANALYST TOTAL BY JOB 6000 MANAGER TOTAL BY JOB 8275 SALESMAN TOTAL BY JOB 5600 PRESIDENT TOTAL BY JOB 5000 10 TOTAL BY DEPT 8750 20 TOTAL BY DEPT 10875 30 TOTAL BY DEPT 9400 GRAND TOTAL FOR TABLE 29025 What's great about GROUPING SETS is that it allows you to define the groups. The GROUPING SETS clause in the preceding query causes groups to be created by DEPTNO, by JOB, by the combination of DEPTNO and JOB, and finally the empty parenthesis requests a grand total. GROUPING SETS gives you enormous flexibility for creating reports with different levels of aggregation; for example, if you wanted to modify the preceding example to exclude the GRAND TOTAL, simply modify the GROUPING SETS clause by excluding the empty parentheses: /* no grand total */ select deptno, job, case grouping(deptno)||grouping(job) when '00' then 'TOTAL BY DEPT AND JOB' when '10' then 'TOTAL BY JOB' when '01' then 'TOTAL BY DEPT' when '11' then 'GRAND TOTAL FOR TABLE' end category, sum(sal) sal from emp group by grouping sets ((deptno),(job),(deptno,job)) DEPTNO JOB CATEGORY SAL ------ --------- --------------------- ---------- 10 CLERK TOTAL BY DEPT AND JOB 1300 20 CLERK TOTAL BY DEPT AND JOB 1900 30 CLERK TOTAL BY DEPT AND JOB 950 20 ANALYST TOTAL BY DEPT AND JOB 6000 10 MANAGER TOTAL BY DEPT AND JOB 2450 20 MANAGER TOTAL BY DEPT AND JOB 2975 30 MANAGER TOTAL BY DEPT AND JOB 2850 30 SALESMAN TOTAL BY DEPT AND JOB 5600 10 PRESIDENT TOTAL BY DEPT AND JOB 5000 CLERK TOTAL BY JOB 4150 ANALYST TOTAL BY JOB 6000 ANAGER TOTAL BY JOB 8275 SALESMAN TOTAL BY JOB 5600 PRESIDENT TOTAL BY JOB 5000 10 TOTAL BY DEPT 8750 20 TOTAL BY DEPT 10875 30 TOTAL BY DEPT 9400 You can also eliminate a subtotal, such as the one on DEPTNO, simply by omitting (DEPTNO) from the GROUPING SETS clause: /* no subtotals by DEPTNO */ select deptno, job, case grouping(deptno)||grouping(job) when '00' then 'TOTAL BY DEPT AND JOB' when '10' then 'TOTAL BY JOB' when '01' then 'TOTAL BY DEPT' when '11' then 'GRAND TOTAL FOR TABLE' end category, sum(sal) sal from emp group by grouping sets ((job),(deptno,job),( )) order by 3 DEPTNO JOB CATEGORY SAL ------ --------- --------------------- ---------- GRAND TOTAL FOR TABLE 29025 10 CLERK TOTAL BY DEPT AND JOB 1300 20 CLERK TOTAL BY DEPT AND JOB 1900 30 CLERK TOTAL BY DEPT AND JOB 950 20 ANALYST TOTAL BY DEPT AND JOB 6000 20 MANAGER TOTAL BY DEPT AND JOB 2975 30 MANAGER TOTAL BY DEPT AND JOB 2850 30 SALESMAN TOTAL BY DEPT AND JOB 5600 10 PRESIDENT TOTAL BY DEPT AND JOB 5000 10 MANAGER TOTAL BY DEPT AND JOB 2450 CLERK TOTAL BY JOB 4150 SALESMAN TOTAL BY JOB 5600 PRESIDENT TOTAL BY JOB 5000 MANAGER TOTAL BY JOB 8275 ANALYST TOTAL BY JOB 6000 As you can see, GROUPING SETS makes it very easy indeed to play around with totals and subtotals in order to look at your data from different angles. PostgreSQL and MySQLThe first step is to use the aggregate function SUM and group by both DEPTNO and JOB: select deptno, job, 'TOTAL BY DEPT AND JOB' as category, sum(sal) as sal from emp group by deptno, job DEPTNO JOB CATEGORY SAL ------ --------- --------------------- ------- 10 CLERK TOTAL BY DEPT AND JOB 1300 10 MANAGER TOTAL BY DEPT AND JOB 2450 10 PRESIDENT TOTAL BY DEPT AND JOB 5000 20 CLERK TOTAL BY DEPT AND JOB 1900 20 ANALYST TOTAL BY DEPT AND JOB 6000 20 MANAGER TOTAL BY DEPT AND JOB 2975 30 CLERK TOTAL BY DEPT AND JOB 950 30 MANAGER TOTAL BY DEPT AND JOB 2850 30 SALESMAN TOTAL BY DEPT AND JOB 5600 The next step is to UNION ALL the sum of all the salaries by JOB: select deptno, job, 'TOTAL BY DEPT AND JOB' as category, sum(sal) as sal from emp group by deptno, job union all select null, job, 'TOTAL BY JOB', sum(sal) from emp group by job DEPTNO JOB CATEGORY SAL ------ --------- --------------------- ------- 10 CLERK TOTAL BY DEPT AND JOB 1300 10 MANAGER TOTAL BY DEPT AND JOB 2450 10 PRESIDENT TOTAL BY DEPT AND JOB 5000 20 CLERK TOTAL BY DEPT AND JOB 1900 20 ANALYST TOTAL BY DEPT AND JOB 6000 20 MANAGER TOTAL BY DEPT AND JOB 2975 30 CLERK TOTAL BY DEPT AND JOB 950 30 MANAGER TOTAL BY DEPT AND JOB 2850 30 SALESMAN TOTAL BY DEPT AND JOB 5600 ANALYST TOTAL BY JOB 6000 CLERK TOTAL BY JOB 4150 MANAGER TOTAL BY JOB 8275 PRESIDENT TOTAL BY JOB 5000 SALESMAN TOTAL BY JOB 5600 The next step is to UNION ALL the sum of all the salaries by DEPTNO: select deptno, job, 'TOTAL BY DEPT AND JOB' as category, sum(sal) as sal from emp group by deptno, job union all select null, job, 'TOTAL BY JOB', sum(sal) from emp group by job union all select deptno, null, 'TOTAL BY DEPT', sum(sal) from emp group by deptno DEPTNO JOB CATEGORY SAL ------ --------- --------------------- ------- 10 CLERK TOTAL BY DEPT AND JOB 1300 10 MANAGER TOTAL BY DEPT AND JOB 2450 10 PRESIDENT TOTAL BY DEPT AND JOB 5000 20 CLERK TOTAL BY DEPT AND JOB 1900 20 ANALYST TOTAL BY DEPT AND JOB 6000 20 MANAGER TOTAL BY DEPT AND JOB 2975 30 CLERK TOTAL BY DEPT AND JOB 950 30 MANAGER TOTAL BY DEPT AND JOB 2850 30 SALESMAN TOTAL BY DEPT AND JOB 5600 ANALYST TOTAL BY JOB 6000 CLERK TOTAL BY JOB 4150 MANAGER TOTAL BY JOB 8275 PRESIDENT TOTAL BY JOB 5000 SALESMAN TOTAL BY JOB 5600 10 TOTAL BY DEPT 8750 20 TOTAL BY DEPT 10875 30 TOTAL BY DEPT 9400 The final step is to UNION ALL the sum of all salaries in table EMP: select deptno, job, 'TOTAL BY DEPT AND JOB' as category, sum(sal) as sal from emp group by deptno, job union all select null, job, 'TOTAL BY JOB', sum(sal) from emp group by job union all select deptno, null, 'TOTAL BY DEPT', sum(sal) from emp group by deptno union all select null,null, 'GRAND TOTAL FOR TABLE', sum(sal) from emp DEPTNO JOB CATEGORY SAL ------ --------- --------------------- ------- 10 CLERK TOTAL BY DEPT AND JOB 1300 10 MANAGER TOTAL BY DEPT AND JOB 2450 10 PRESIDENT TOTAL BY DEPT AND JOB 5000 20 CLERK TOTAL BY DEPT AND JOB 1900 20 ANALYST TOTAL BY DEPT AND JOB 6000 20 MANAGER TOTAL BY DEPT AND JOB 2975 30 CLERK TOTAL BY DEPT AND JOB 950 30 MANAGER TOTAL BY DEPT AND JOB 2850 30 SALESMAN TOTAL BY DEPT AND JOB 5600 ANALYST TOTAL BY JOB 6000 CLERK TOTAL BY JOB 4150 MANAGER TOTAL BY JOB 8275 PRESIDENT TOTAL BY JOB 5000 SALESMAN TOTAL BY JOB 5600 10 TOTAL BY DEPT 8750 20 TOTAL BY DEPT 10875 30 TOTAL BY DEPT 9400 GRAND TOTAL FOR TABLE 29025 |
EAN: 2147483647
Pages: 235