Rollup as aggregation enhancement

This morning I wrote a query with a GROUP BY clause. Not really complicated. Here’s a worked example:

select department_id department
      ,job_id job
      ,round(avg(salary)) average
      ,round(sum(salary)) total
 from hr.employees
 where department_id in ( 80 ,50)
 group by job_id,department_id

The result, the total and the average salary of two departments grouped by department and job.

What’s missing in this report is the total and average salaries of the entire group and the total and average salary per department, regardless of the job.

After some thought and typing I came up with this solution:

select department_id department
      ,job_id job
      ,round(avg(salary)) average
      ,round(sum(salary)) total
  from hr.employees
 where department_id in( 80 ,50)
 group by department_id, job_id
 union all
select department_id department
      ,null job
      ,round(avg(salary)) average
      ,round(sum(salary)) total
  from hr.employees
 where department_id in( 80 ,50)
 group by department_id
 union all
select null department
      ,null job
      ,round(avg(salary)) average
      ,round(sum(salary)) total
  from hr.employees
 where department_id in( 80 ,50)

The result of this query:

Quite a long query. There must be a better solution I thought. Perhaps one of the analytic functions. Eventually I found the solution, with the same rusult of course in (one of) the aggregation enhancements, called, ‘ROLLUP’.

select department_id department
      ,job_id job
      ,round(avg(salary)) average
      ,round(sum(salary)) total
  from hr.employees
 where department_id in( 80 ,50)
 group by rollup (department_id,job_id)

For me it was an eyeopener, so beautiful, so compact, whow!

• ROLLUP (and CUBES) are available since Oracle 9i.
• More about analytic functions: Oracle Technical White Paper

Ratings:

VN:F [1.9.13_1145]

please wait...

Rating: 0.0/5 (0 votes cast)

• 

 Print This PDF Version