Oracle: replace "rollup" in query with something else

I need to rewrite a simple query without rollup function. Could you help me?

This is 开发者_高级运维an original query:

  SELECT e.department_id, 
         e.job_id, 
         SUM(e.salary)
    FROM EMPLOYEES e
GROUP BY ROLLUP(e.department_id, e.job_id);

I guess it is possible to rewrite using UNION statement, yea?

No need for a UNION, you can use GROUPING SETS. This will produce the same results and even the same explain plan:

  SELECT e.department_id, 
         e.job_id, 
         SUM(e.salary)
    FROM EMPLOYEES e
GROUP BY GROUPING SETS( (e.department_id, e.job_id), (e.department_id), () )

The following should return the same result as a rollup, but with worse performance and less controll over the "levels".

select e.department_id
      ,e.job_id
      ,SUM(e.salary)
  from EMPLOYEES e
 group 
    by e.department_id
      ,e.job_id
union all
select e.department_id
      ,null
      ,SUM(e.salary)
  from EMPLOYEES e
 group 
    by e.department_id
union all
select null
      ,null
      ,SUM(e.salary)
  from EMPLOYEES e;

You can use a CTE to handle it (note I created the EMPLOYEE table via the connect by just to have sample data). There are probably better ways to do this, but this is a way!

WITH EMPLOYEES AS(
    SELECT   MOD(LEVEL,5)  DEPARTMENT_ID 
           , LEVEL JOB_ID 
           , 1000*LEVEL SALARY
      FROM DUAL      
     CONNECT BY LEVEL < 10
)
, SUMMEDDATA AS(
  SELECT e.department_id, 
         e.job_id, 
         SUM(e.salary) SUMMED_SALARY
    FROM EMPLOYEES e
GROUP BY e.department_id, e.job_id
)
, SUMMEDJOB_ID AS(
  SELECT e.department_id, 
         SUM(e.salary) SUMMED_SALARY
    FROM EMPLOYEES e
GROUP BY e.department_id
)
, SUMMEDTOTAL AS(
  SELECT  
         SUM(e.salary) SUMMED_SALARY
    FROM EMPLOYEES e
)
SELECT DEPARTMENT_ID ,
       JOB_ID ,
       SUMMED_SALARY
  FROM SUMMEDDATA
UNION ALL
SELECT DEPARTMENT_ID ,
       NULL ,
       SUMMED_SALARY
  FROM SUMMEDJOB_ID
UNION ALL
SELECT NULL ,
       NULL ,
       SUMMED_SALARY
  FROM SUMMEDTOTAL
ORDER BY 1 NULLS LAST, 2 NULLS LAST ;

DEPARTMENT_ID          JOB_ID                 SUMMED_SALARY          
---------------------- ---------------------- ---------------------- 
0                      5                      5000                   
0                                             5000                   
1                      1                      1000                   
1                      6                      6000                   
1                                             7000                   
2                      2                      2000                   
2                      7                      7000                   
2                                             9000                   
3                      3                      3000                   
3                      8                      8000                   
3                                             11000                  
4                      4                      4000                   
4                      9                      9000                   
4                                             13000   
                                              45000