Lessons › SQL › Deep Dive: Advanced Window Functions

SQL lesson

GROUPING SETS

GROUPING SETS lets you define multiple grouping levels in a single query, so the database returns several different summaries all at once.

Example

Creating Multiple Retail Subtotals with GROUPING SETS

SELECT category, region, SUM(revenue) AS total_revenue
FROM orders
GROUP BY GROUPING SETS ((category), (region), ())

Result:

Step by step

1. Run the query without SUM so you can see what the unfiltered values look like.

   SELECT category, region, revenue
   FROM orders
   GROUP BY GROUPING SETS ((category), (region), ());

2. SUM totals the numbers in a column. Works on numeric columns only.

   SELECT category, region, SUM(revenue)
   FROM orders
   GROUP BY GROUPING SETS ((category), (region), ());

3. AS gives the SUM result a friendly column name so the output reads cleanly.

   SELECT category, region, SUM(revenue) AS total_revenue
   FROM orders
   GROUP BY GROUPING SETS ((category), (region), ());

Practice

Try each one in your head first, then open the answer.

SELECT order_id, customer_id, COUNT(*) FROM orders GROUP BY GROUPING SETS ((order_id), (customer_id), ())

SELECT sale_id, store_id, COUNT(*) FROM store_sales GROUP BY GROUPING SETS ((sale_id), (store_id), ())

SELECT product_id, brand, COUNT(*) FROM products GROUP BY GROUPING SETS ((product_id), (brand), ())

SELECT review_id, product_id, COUNT(*) FROM reviews GROUP BY GROUPING SETS ((review_id), (product_id), ())

SELECT employee_id, full_name, COUNT(*) FROM employees GROUP BY GROUPING SETS ((employee_id), (full_name), ())