SQL GROUPING SETS

This post is a continuation of a series on OLAP SQL.  OLAP refers to Online Anayltical Processing used in data warehousing transactions.  The Grouping Sets clause in SQL allows you to see the results of a query in different groups.

For example, suppose you wanted to see a report of sales by sales reps but also wanted to see sales grouped by week and territory.  You could write two queries to obtain two reports.  Or you could write one query that used the Union All operator on the two groupings.

This is where the Grouping sets clause in SQL comes in handy.  Grouping Sets allows you to define multiple groups in one data set.  The advantage is that the underlying table is accessed only once which speeds up query performance on large data sets.  The syntex for Grouping Sets is:

SELECT col1,col2,count(*)
FROM table1
WHERE.....
GROUP BY GROUPING SETS((col1,col2),(col3,col4));

Here is an example using the hr schema in Oracle:

SELECT department_id,job_id,count(*)
FROM employees
GROUP BY GROUPING SETS(department_id,job_id);

The result set would like this:

DEPARTMENT_ID	JOB_ID	COUNT(*)
	IT_PROG	5
	AC_MGR	1
	AC_ACCOUNT	1
	ST_CLERK	20
	HR_REP	1
100		6
30		6
90		3
20		2
70		1
110		2

As you can see, the Grouping By Sets function offers efficient processing of large data sets than querying two  tables separately.