This query generated a single result set with the aggregates for all grouping sets.
Even though the above query works as you expected, it has two main problems.
- First, it is quite lengthy.
- Second, it has a performance issue because PostgreSQL has to scan the
sales
table separately for each query.
To make it more efficient, PostgreSQL provides the GROUPING SETS
clause which is the subclause of the GROUP BY
clause.
The GROUPING SETS
allows you to define multiple grouping sets in the same query.
The general syntax of the GROUPING SETS
is as follows:
SELECT c1, c2, aggregate_function(c3) FROM table_name GROUP BY GROUPING SETS ( (c1, c2), (c1), (c2), () );
Code language: SQL (Structured Query Language) (sql)
In this syntax, we have four grouping sets (c1,c2)
, (c1)
, (c2)
, and ()
.
To apply this syntax to the above example, you can use GROUPING SETS
clause instead of the UNION ALL
clause like this:
SELECT brand, segment, SUM (quantity) FROM sales GROUP BY GROUPING SETS ( (brand, segment), (brand), (segment), () );
Code language: SQL (Structured Query Language) (sql)