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?

Back to index of questions


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                                               

           CLARK            2450                                                

           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 : '

 END AS dept_Name,

 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                               

                            CLARK            2450                              

                            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


 

Back to top

Back to index of questions