August Top Tip

If you want to create a query that will provide subtotals and grand totals as well as standard aggregate totals, use ROLLUP - Oracle's extension to the group by clause.

Queries based on the standard HR Schema
Subtotals and Grand Totals:

SELECT   department_id
              , last_name
              , sum(salary) sal
FROM      employees
GROUP BY ROLLUP (department_id, last_name);

To generate just Grand Totals, utilise the GROUPING function (returns 1 or 0 based on whether a non - aggregate column is used in the aggregate calculation)

SELECT   department_id
              , last_name
              , sum(salary) sal
FROM     employees
GROUP BY ROLLUP (department_id, last_name)
HAVING  grouping(department_id) = 1
OR          grouping(department_id)+grouping(last_name)=0;

Tip: Add Grouping(column name) to the select clause to see the values used.