GROUPING SETS for multiple groupings in PostgreSQL

Introduction

GROUPING SETS lets you group data in different ways in one query. It helps you get multiple summaries without writing many queries.

You want to see sales totals by product and by region in one report.

You need to compare monthly and yearly totals together.

You want to get counts by category and by subcategory at the same time.

You want to reduce the number of queries by grouping data in multiple ways at once.

Syntax

PostgreSQL

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
GROUP BY GROUPING SETS (
  (grouping_column1, grouping_column2, ...),
  (grouping_column3, ...),
  ...
);

Each set inside GROUPING SETS is a grouping you want to do.

You can list as many grouping sets as you want, separated by commas.

Examples

This groups sales by region and also by product separately.

PostgreSQL

SELECT region, product, SUM(sales)
FROM sales_data
GROUP BY GROUPING SETS ((region), (product));

This shows totals by year and also by year and month together.

PostgreSQL

SELECT year, month, SUM(amount)
FROM payments
GROUP BY GROUPING SETS ((year), (year, month));

This counts items by category and also by subcategory separately.

PostgreSQL

SELECT category, subcategory, COUNT(*)
FROM items
GROUP BY GROUPING SETS ((category), (subcategory));

Sample Program

This example creates a sales table, inserts some data, and then uses GROUPING SETS to get total sales by region and by product in one query.

PostgreSQL

CREATE TABLE sales (
  region TEXT,
  product TEXT,
  sales_amount INT
);

INSERT INTO sales VALUES
('North', 'Apples', 100),
('North', 'Oranges', 150),
('South', 'Apples', 200),
('South', 'Oranges', 100);

SELECT region, product, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY GROUPING SETS ((region), (product));

Important Notes

GROUPING SETS is useful to combine multiple GROUP BY queries into one.

Empty values in the output mean that column is not part of that grouping set.

You can also use ROLLUP or CUBE for automatic grouping sets, but GROUPING SETS gives you full control.

Summary

GROUPING SETS lets you group data in many ways in one query.

It saves time and makes reports easier by combining multiple groupings.

Use it when you want different summaries together without multiple queries.