The SQL ROLLUP Function

As a SQL programmer, if you need to calculate subtotals and a grand total in a query, the SLQ ROLLUP function fits the bill. The ROLLUP function is OLAP(Online Analytical Processing) SQL often used in data warehousing transactions.

Lets say you have an employee table with dept_id and job_id foreign keys to department and job tables. To determine the number of employees in each department and in each job position with subtotals and a grand total, you could write the following query:

SELECT department_id,job_id,Count(*) FROM employees
GROUP BY ROLLUP(department_id,job_id);

This query will produce the following outcome:

DEPT_ID    JOB_ID               COUNT(*)
---------------------- ---------- ---------

50	ST_MAN	5
50	SH_CLERK	20
50	ST_CLERK	20
50		45	Note: department subtotal
110	AC_MGR	1
110	AC_ACCOUNT	1
110		2
		107	Note: grand total

As you can see, the ROLLUP function calculates subtotals by job and department and a grand total all in one statement.