SQL Grouping Sets

This post in a continuation of a series discussing OLAP SQL. OLAP stands for Online Analytical Processing, often used in data warehousing transactions. In Oracle, Grouping Sets are an extension of the Group By clause that enables specifying multiple groups of data.

With Grouping Sets, you just specify the desired groups and the Oracle database computes all groupings specified in the Grouping Sets clause and combines the results in a Union All operation. The Union All operation allows duplicate rows in the result set. The syntax is depicted below:

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

Without the Grouping Sets syntax, you would have to run separate queries and then do a Union operation. This can be inefficient if several groupings are required and can make your SQL code more complex. With Grouping Sets, this is can be done in one step and is more efficient as the underlying table is accessed only once.