What does GROUP BY do

The GROUP BY clause collapses multiple table rows that share identical values across specified columns into distinct summary rows, allowing mathematical aggregate functions (COU...

🟢 Junior Level

The GROUP BY clause collapses multiple table rows that share identical values across specified columns into distinct summary rows, allowing mathematical aggregate functions (COUNT, SUM, AVG, MIN, MAX) to be computed across each group.

Simple Analogy

Imagine you have a large crate of mixed fruit. GROUP BY fruit_type sorts the fruit into distinct piles (Apples, Bananas, Oranges). Once the fruit is separated into individual piles, you can perform group-level calculations:

Count how many fruits are in each pile (COUNT).
Calculate the total weight of each pile (SUM).
Find the average weight per fruit type (AVG).

-- Table 'orders':
-- customer_id | amount
-- 1           | 100
-- 1           | 250
-- 2           | 150

-- Group orders by customer_id and calculate total expenditure per customer
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;

-- Output:
-- customer_id | total_spent
-- 1           | 350
-- 2           | 150

Standard Aggregate Functions

COUNT(*): Total count of rows in the group.
COUNT(column): Count of non-NULL values in the group.
SUM(column): Arithmetic sum of numeric values.
AVG(column): Arithmetic mean.
MIN(column) / MAX(column): Minimum and maximum values.

🟡 Middle Level

Physical Execution Engines: `HashAggregate` vs. `GroupAggregate`

In PostgreSQL, the query optimizer selects between two distinct physical algorithms to execute a GROUP BY:

                           ┌────────────────────────┐
                           │   GROUP BY customer_id │
                           └───────────┬────────────┘
                                       │
                    ┌──────────────────┴──────────────────┐
                    ▼                                     ▼
          ┌───────────────────┐                 ┌───────────────────┐
          │   HashAggregate   │                 │  GroupAggregate   │
          │  Unsorted inputs  │                 │   Sorted inputs   │
          │   Uses work_mem   │                 │  Streaming RAM    │
          └───────────────────┘                 └───────────────────┘

1. `HashAggregate` (Default for Unsorted Inputs)

Builds an in-memory hash table in work_mem where the hash key is the grouping column(s) and the value is the aggregate accumulator state.
Streams through the table, computes hash(group_key) for each row, and updates accumulators in-place.
Time Complexity: $O(N)$ linear time.
Output Order: Unsorted (emits rows in arbitrary hash bucket order).

2. `GroupAggregate` (For Sorted Inputs)

Requires the input data to be sorted by the grouping columns (either via an existing B-tree Index Scan or an explicit Sort node).
Streams contiguous sorted rows. When the group key changes, it emits the summary row and resets accumulators.
Advantage: Requires near-zero memory ($O(1)$) because it only retains state for the single active group.
Time Complexity: $O(N)$ if pre-sorted via index; $O(N \log N)$ if sorting is required.

Grouping by Primary Key (Functional Dependency)

In standard SQL-92, every non-aggregated column in the SELECT list was required to appear explicitly in the GROUP BY clause. In modern PostgreSQL (SQL:1999+ compliance), grouping by a table’s PRIMARY KEY automatically satisfies Functional Dependency:

-- ✅ VALID in PostgreSQL: Grouping by Primary Key unlocks all columns from 'customers'
SELECT c.customer_id, c.name, c.email, COUNT(o.order_id) AS total_orders
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id; -- 'name' and 'email' do NOT need to be in GROUP BY!

Because customer_id is unique, the database mathematically guarantees that name and email can have only one possible value per customer_id.

Handling `NULL` Values in Grouping

In standard SQL relational grouping, all NULL values are treated as matching and collapsed into a single distinct group:

SELECT status, COUNT(*) 
FROM orders 
GROUP BY status;

-- Output:
-- 'COMPLETED' | 850
-- 'PENDING'   | 140
-- NULL        | 10   <-- All NULL rows aggregated into one group

🔴 Senior Level

`HashAggregate` Disk Spilling (PostgreSQL 13+)

Prior to PostgreSQL 13: If the number of unique groups in a HashAggregate exceeded work_mem, PostgreSQL aborted with an error or fell back to an expensive on-disk external merge sort.
PostgreSQL 13+: HashAggregate supports disk spilling. When work_mem is saturated, hash partitions are serialized into temporary files on disk and processed in batches.

EXPLAIN (ANALYZE, BUFFERS)
SELECT category_id, COUNT(*) FROM massive_catalog GROUP BY category_id;
-- HashAggregate (cost=... Memory Usage: 4096kB, Disk: 280MB, Batches: 8) <-- DISK SPILL!

Remediation: Expand work_mem for analytical queries (SET work_mem = '256MB') to keep group accumulation entirely in RAM.

Multi-Dimensional Grouping: `ROLLUP`, `CUBE`, `GROUPING SETS`

Instead of running multiple expensive queries combined with UNION ALL, PostgreSQL computes multi-dimensional subtotals in a single table pass:

1. `ROLLUP` (Hierarchical Subtotals)

SELECT department, region, SUM(sales)
FROM sales_data
GROUP BY ROLLUP (department, region);
-- Computes:
-- 1. (department, region) detail
-- 2. (department, NULL) department subtotals
-- 3. (NULL, NULL) grand total

2. `CUBE` (All Dimensional Combinations — $2^N$)

SELECT department, region, SUM(sales)
FROM sales_data
GROUP BY CUBE (department, region);
-- Computes: (dept, reg), (dept, null), (null, reg), (null, null)

3. `GROUPING SETS` (Selective Custom Aggregation)

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

Parallel Aggregation Architecture

When executing GROUP BY across millions of rows, PostgreSQL divides the workload across background workers:

[Parallel Workers] ──► Partial HashAggregate ──► Gather ──► Finalize HashAggregate ──► Client

Partial Aggregation: Each worker scans a portion of the table in parallel and builds a local hash table of partial aggregate states (e.g., local count and local sum).
Gather: The leader process collects the partial states from all workers.
Finalize Aggregation: The leader combines partial counts and sums to produce exact global metrics (e.g., $\text{final_avg} = \frac{\sum \text{sum}}{\sum \text{count}}$).

4 Tricky Questions

1. Does `GROUP BY` guarantee that output rows are returned in sorted order? Why was this a common misconception in older databases?

Answer:
No, GROUP BY provides absolutely zero ordering guarantees.
In modern PostgreSQL, the optimizer predominantly chooses HashAggregate for unsorted data, which outputs rows in non-deterministic hash bucket order.
Historical Context: Decades ago in early database implementations (including older MySQL and early Postgres versions), grouping was implemented almost exclusively using sorting (GroupAggregate). As a side effect, results appeared sorted on screen, creating the enduring myth that GROUP BY sorts data. In modern SQL, only an explicit ORDER BY clause guarantees output order.

2. Why does `SELECT u.id, u.name, u.email, COUNT(o.id) ... GROUP BY u.id` compile in PostgreSQL, but fail in older SQL engines with “column must appear in the GROUP BY clause”?

Answer:
Because PostgreSQL implements the Functional Dependency rule from the SQL:1999 standard (enhanced in PostgreSQL 9.1).
If the grouping column (u.id) is verified by the catalog as the PRIMARY KEY of the table, every other column of that table (name, email) is functionally dependent on u.id (meaning for every unique ID, there can exist exactly one name and one email). The database engine recognizes that grouping by u.id alone guarantees ambiguity-free values for name and email, making their inclusion in GROUP BY redundant. Older engines lacking functional dependency analysis reject the query.

3. How does `ROLLUP (a, b)` differ internally from executing `UNION ALL` across three separate `GROUP BY` queries?

Answer:

Writing three separate queries connected with UNION ALL (GROUP BY a, b + GROUP BY a + Grand Total) forces PostgreSQL to perform three independent table scans and three separate aggregation routines, multiplying I/O and CPU costs threefold.
ROLLUP (a, b) executes in a single sequential scan. The engine reads each row once and feeds it simultaneously into multiple in-memory aggregation accumulators. It executes 3x–5x faster and generates 1/3 of the buffer cache traffic.

4. How does parallel aggregation compute an `AVG()` function across multiple worker processes without losing precision?

Answer:
An average cannot be computed by averaging worker sub-averages ($\text{avg}(A, B) \neq \frac{\text{avg}(A) + \text{avg}(B)}{2}$ unless row counts are identical).
PostgreSQL’s parallel aggregation engine splits aggregate functions into two internal transition functions:

Partial Phase (Workers): Each worker computes an internal transition state consisting of a tuple: (count, sum).
Finalize Phase (Leader): The leader receives the (count, sum) tuples from all workers, sums the total counts, sums the total amounts, and evaluates the final division: $\text{Final AVG} = \frac{\sum \text{Worker Sums}}{\sum \text{Worker Counts}}$ This guarantees bit-exact arithmetic precision across distributed parallel workers.

🎯 Interview Cheat Sheet

Core Concepts

Definition: Collapses multiple rows with identical keys into distinct summary buckets.
2 Physical Engines:
- HashAggregate: Uses in-memory hash table in work_mem; $O(N)$ execution; unsorted output; spills to disk in PG 13+.
- GroupAggregate: Reads pre-sorted input (B-tree index); streaming $O(1)$ memory; sorted output.
NULL Handling: All NULLs are grouped together into a single summary row.

Advanced Capabilities

Functional Dependency: Grouping by Primary Key allows selecting any column from that table without adding them to GROUP BY.
Multi-Dimensional Grouping:
- ROLLUP: Hierarchical subtotals + grand total.
- CUBE: All $2^N$ dimensional combinations.
- GROUPING SETS: Custom selected grouping sets in a single table pass.
Parallel Aggregation: Workers build Partial Aggregate $\rightarrow$ Leader combines and runs Finalize Aggregate.

Red Flags (What NOT to Say)

❌ “GROUP BY always sorts the output.” (HashAggregate produces non-deterministic hash bucket order; always use ORDER BY).
❌ “You must always list every selected column in GROUP BY.” (Grouping by Primary Key satisfies functional dependency).
❌ “To compute department subtotals and a grand total, UNION ALL is the standard way.” (Use ROLLUP to compute subtotals in a single pass).

Difference between WHERE and HAVING — Pre-aggregation vs post-aggregation filtering
When to use HAVING — Filtering on aggregate calculations
What are Window Functions — Calculating aggregates without collapsing rows
What is Explain plan — Identifying HashAggregate vs GroupAggregate in plans

🟢 Junior Level

Simple Analogy

Standard Aggregate Functions

🟡 Middle Level

Physical Execution Engines: HashAggregate vs. GroupAggregate

1. HashAggregate (Default for Unsorted Inputs)

2. GroupAggregate (For Sorted Inputs)

Grouping by Primary Key (Functional Dependency)

Handling NULL Values in Grouping

🔴 Senior Level

HashAggregate Disk Spilling (PostgreSQL 13+)

Multi-Dimensional Grouping: ROLLUP, CUBE, GROUPING SETS

1. ROLLUP (Hierarchical Subtotals)

2. CUBE (All Dimensional Combinations — $2^N$)

3. GROUPING SETS (Selective Custom Aggregation)