The Oracle (tm) Users' Co-Operative FAQ
How do I generate both report header and summation row at each aggregation level in a SQL Statement?
|
Author's name: Frank Zhou Author's Email:zhou328@comcast.net |
Date written: 20th May 2005 Oracle version(s): 9.2 |
|
How do I generate both the report header and summation row at each aggregation level in a SQL Statement? |
The following SQL query pattern can be used to generate both the report header and summation row at each aggregation level in a SQL Statement.
SQL> break on dept
SQL> select dept, ename, sal from emp_t
2 order
by dept, sal
3 /
DEPT ENAME SAL
---------- ----------
----------
MGMT SMITH 800
MARTIN 1250
WARD 1250
MILLER 1300
TURNER 1500
FORD 3000
SCOTT 3000
SALES ALLEN 1600
BLAKE 2850
SUPPORT JAMES 950
DEPT ENAME SAL
---------- ----------
----------
SUPPORT JONES 2975
KING 5000
13 rows selected.
SQL> SELECT
CASE WHEN Grp_Id = 3 AND lev = 1
THEN 'Summary Report
Begin'
WHEN Grp_Id = 3
AND lev = 2
THEN 'Summary Total : '
WHEN Grp_Id = 1
AND lev = 1
THEN rpad(' ',8,' ')||dept||' '||'Begin'
WHEN Grp_Id = 1
AND lev = 2
THEN rpad(' ',8,' ')||dept||' '||'Total : '
ename,
CASE WHEN (Grp_Id IN (1,3 ) AND lev = 2 )
OR (Grp_Id = 0 AND lev =1 )
THEN sal END AS salary
FROM
(SELECT dept, sum(sal) sal, ename, grouping_id(dept, ename) as Grp_Id
FROM
emp_t
GROUP BY ROLLUP (dept, ename) ),
(SELECT level lev FROM dual CONNECT BY LEVEL <=2)
WHERE (Grp_Id = 0 AND lev = 1 ) OR ( Grp_Id > 0 )
ORDER BY CASE WHEN lev = 1 AND Grp_Id = 3 THEN ' '
ELSE dept END NULLS LAST,
CASE WHEN lev = 1 AND Grp_Id = 1 THEN '0'
WHEN lev = 2 AND Grp_Id = 1 THEN '2'
ELSE '1' END, sal
DEPT_NAME ENAME SALARY
---------------------------
---------- ----------
Summary Report Begin
MGMT Begin
SMITH 800
WARD 1250
MARTIN 1250
MILLER 1300
TURNER 1500
SCOTT 3000
FORD 3000
MGMT Total : 12100
SALES Begin
ALLEN 1600
BLAKE 2850
SALES Total : 6900
SUPPORT Begin
JAMES 950
JONES 2975
KING 5000
SUPPORT Total : 8925
Summary Total : 27925
21 rows selected.
SQL> spool off
