Wednesday, March 10, 2010

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  45 Note: department subtotal
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.

No comments:

Post a Comment

Get your own Widget