Oracle: rollup totals

Oracle has a nice feature that allows to show a grand total. I assume you want to show a line with a grand total when a grouping by SQL is executed. Something that shows the number of employees per department and the total number of employees.

That can be done by a simple change in the traditional group by SQL:

select department_id , to_char(sum(salary),'999999d00') avg_sal, count(*) as Nmbr_Employees
 FROM
 employees
 group by ROLLUP (department_id );

The last line then provides a grand total:

One may be puzzled by the usage of “null”: does it indicate a null as no department is found or does it refer to a “null” as it is a grand total where not all departments are known.

This can be solved by adding one additional column that indicates the level. This one column is invoked by “grouping(column) alias” as in:

select  decode ( GROUPING (department_id), 1,'Grand total' ,
                                            0,nvl(to_char(department_id ),                      'no dept') ) dept_id  , 
 sum(salary) sum_sal 
 FROM
 employees
 group by rollup( department_id  )

It is possible to extend the number of subtotals. So if you have two or more items in the group by clause (say region, country and city ), one may want to calculate the subtotals for region, country and city. This can be done with a small extension of previous SQL:

This also allows us to create totals per level. See:

select region, country,city, sum(amount)   
from info_sales 
group by  rollup (region, country,city); 

This is the way to create hierarchical data. Grand totals are given, along with totals per region, per country and per city.