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:
A visual of this would look like:
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).
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.