Using GROUP BY

Introduction

Used to group rows of data with the same values in specified columns into a single row, with the main purpose being accompanied by Aggregate Functions such as SUM, AVG, COUNT, MAX, MIN.
Note that all columns appearing in the SELECT clause that are not part of an aggregate function like SUM, COUNT must be declared in the GROUP BY clause.

How it works

When you execute a GROUP BY command, the Postgres Optimizer typically chooses one of the following two algorithms depending on the data volume and work_mem:

HashAggregate: Uses a hashing algorithm
- Postgres scans through the data table once, hashes the value of the GROUP BY column for each row into a bucket in a Hash Table located in RAM and computes the aggregate function into that bucket if it already exists.
- Used when data is unsorted and the hash table can fit entirely within work_mem, which is usually the fastest method with a time complexity of O(N) since it only requires reading the table once after creating the Hash Table for small and medium datasets.
GroupAggregate: Uses a Sort/Merge algorithm
- This algorithm requires the data to be sorted beforehand by the GROUP BY columns and if the data is unsorted, Postgres will execute a Sort step using the External Merge Sort algorithm if the data is too large and must be written to disk, then performs a merge-like scan from top to bottom to group consecutive identical rows together.
- Used when the dataset is too large to fit in the RAM hash table, or when data is already presorted by an existing element like an Index.
- The main advantage is low memory consumption for processing, but it has a limitation where the initial sorting cost is often quite large.

Grouping Extensions

Postgres also provides Grouping Extensions used alongside GROUP BY to support multidimensional reporting (OLAP - Online Analytical Processing), which are designed to solve the need to view detailed reports, category subtotals and a grand total of the entire data within a single query without combining multiple cumbersome SQL statements using UNION ALL.

GROUP BY ROLLUP: Works by a hierarchical de-escalation mechanism, meaning it progressively drops columns from right to left, groups them and automatically calculates extra subtotal and grand total rows, for example using ROLLUP(A, B) generates groupings across 3 levels:
- Most detailed: (A, B)
- Removing 1 column on the right: (A) -> This represents the Subtotal row for column A.
- Removing the next column (no columns left): () -> This represents the Grand Total row for the entire table.
GROUP BY CUBE: Works by a mechanism that generates all possible combinations (permutations) of the passed columns.
- If you pass N columns, it will generate 2^N groups.
- For example, using CUBE(A, B) will generate 2^2 = 4 grouping combinations: (A, B), (A), (B) and () which is the Grand Total.
GROUP BY GROUPING SETS: This is the most generalized approach, capable of replacing both ROLLUP and CUBE, which does not automatically generate combinations but allows you to precisely define the exact data grouping levels you want to appear in the output.
- This means whichever way you want to group the data, you explicitly specify that exact format.
- For example, if you only want to group by A separately and B separately, without including (A, B) or the Grand Total (), you would use it as GROUP BY GROUPING SETS ((A),(B)).

Detail

Please create the table as follows

CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    order_date DATE,
    category VARCHAR(50),
    sales_channel VARCHAR(20),
    revenue NUMERIC(10, 2)
);

Use GROUP BY and Grouping Extensions as follows

-- Query 1
SELECT 
    category, 
    sales_channel, 
    SUM(revenue) AS total_revenue
FROM sales
GROUP BY category, sales_channel
ORDER BY category, sales_channel;

-- Query 2
SELECT 
    category, 
    sales_channel, 
    SUM(revenue) AS total_revenue
FROM sales
GROUP BY ROLLUP (category, sales_channel)
ORDER BY category NULLS LAST, sales_channel NULLS LAST;

-- Query 3
SELECT 
    category, 
    sales_channel, 
    SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE (category, sales_channel)
ORDER BY category NULLS LAST, sales_channel NULLS LAST;

-- Query 4
SELECT 
    order_date,
    category, 
    SUM(revenue) AS total_revenue
FROM sales
GROUP BY GROUPING SETS (
    (order_date),
    (category)
)
ORDER BY order_date NULLS LAST, category NULLS LAST;

-- Query 5
SELECT 
    CASE WHEN GROUPING(category) = 1 THEN 'All Category' ELSE category END AS category,
    CASE WHEN GROUPING(sales_channel) = 1 THEN 'All Sale Channel' ELSE sales_channel END AS sales_channel,
    SUM(revenue) AS total_revenue
FROM sales
GROUP BY ROLLUP (category, sales_channel)
ORDER BY GROUPING(category), GROUPING(sales_channel);

Query 1: This uses basic GROUP BY

Used to calculate standard total revenue for each category and sales_channel.
Only groups precisely by the specified columns, containing no aggregated rows like Total/Subtotal.

Query 2: Uses GROUP BY ROLLUP to create hierarchical reports resembling a directory tree

Creates a revenue report structured from largest to smallest, including overall platform total revenue, total revenue by individual category and detailed revenue for each sales channel inside that category.
Sub-groupings progressively drop columns from right to left to produce Subtotal and Grand Total rows.
You can observe that using NULLS LAST makes the row with a NULL value in a column signify the total aggregation of all values belonging to that specific column.

Query 3: Uses GROUP BY CUBE to create a multidimensional matrix report

Automatically creates all possible grouping scenarios with 2^N combinations.
Used when exporting reporting outcomes to evaluate total revenue across every combination, such as viewing totals by category, viewing totals by independent sales channels, or analyzing totals for both concurrently.

Query 4: Uses GROUP BY SETS to produce a highly customized report

Helps explicitly define which combinations you want to group, omitting redundant combinations to optimize execution efficiency.
Used when you only want to see 2 distinct reports merged into 1 table, such as a report by Date and a report by product Category, without needing details on sales channels or the overall Grand Total row to avoid visual clutter.

Query 5: Uses the GROUPING function to format the report
- Aggregate rows in output results will display NULL as their title, which might cause confusion with actual NULL data.
- You can utilize the GROUPING function to transform NULL values into descriptive text like All Category / All Sale Channel for clarity.