Using SQL GROUPING SETS for Multiple GROUP BY Queries in a Single Query

SQL GROUPING SETS is a feature of the SQL language that allows you to specify multiple group by sets in a single query.

The best way to demonstrate this is with code and examples.

So imagine you have table of sales data which contained the following fields:

• product
• region
• sale_amount

A visual of this would look like:

product	region	sale_amount
shoes	Alaska	1500
shoes	Texas	2200
shoes	Washington	6000
pants	Alaska	500
pants	Texas	600
pants	Washington	750
coats	Alaska	1725
coats	Texas	750
coats	Washington	1250

Now say you had a requirement to query this table and aggregate the results. To aggregate data using SQL we use GROUP BY which allows us to group data by one or many fields in our data set and then perform aggregate functions such as SUM(), COUNT(), MIN(), MAX() etc amongst others.

Let’s now assume that you had multiple group by requirements based on the available data. So this would be grouping by the available dimension fields, product and/or region.

Fine so, you write your group by queries like this:

SELECT product, SUM(sale_amount) AS sale_amount

FROM sales

GROUP BY product;

Then you write:

SELECT region, SUM(sale_amount) AS sale_amount

FROM sales

GROUP BY region;

Then you write:

SELECT product, region, SUM(sale_amount) AS sale_amount

FROM sales

GROUP BY product, region;

That’s three queries – three hits to the database to aggregate this data and if you want to put the results together, you have to use UNION ALL.

No no no, there is a better way 🙂

Use SQL GROUPING SETS and you can do this in a single query:

SELECT product, region, sum(sale_amount) as sale_amount

FROM sales

GROUP BY GROUPING SETS ((product), (region), (product, region))

This is a really nice and efficient way to aggregate data into multiple groups using a single SQL query. In the returned results, we can see that we have rows based on our GROUPING SETS requirements of GROUP BY product (rows 10-12), GROUP BY region (rows 13-15) and GROUP BY product, region (rows 1-9).

GROUPING SETS is available in database products SQL Server, PostgresSQL and Oracle. MySQL does it a bit differently using WITH ROLLUP.

I’ve written about this in the past looking at performance comparisons of GROUPING SETS v UNION ALL. You can read that one here.

Hope you like the post and if you did, I would love it if you could share it on social media or backlink to it. It would really help to grow this blog. Thank you.