Aggregation and GROUP BY

Overview

Aggregation: Collapse many rows into one per group — count, sum, avg, min, max, string_agg, array_agg, bool_and, …
GROUP BY: Defines the groups; HAVING filters groups after aggregation
Postgres Extras: FILTER (WHERE …) clause, GROUPING SETS / ROLLUP / CUBE, ordered-set aggregates (percentile_cont, mode)
Sample Data: E-commerce Practice Queries

GROUP BY Basics

Rules

Every SELECT Column: Must be in GROUP BY or inside an aggregate
Functional Dependency (PG): If you group by a table’s primary key, you can select any other column of that table
Grouping by Expressions: GROUP BY date_trunc('month', created_at) or by position GROUP BY 1
Aggregates Skip NULLs: Except count(*)

SELECT status,
       count(*)               AS orders,
       min(created_at)::date  AS first_order,
       max(created_at)::date  AS last_order
FROM orders
GROUP BY status
ORDER BY orders DESC;
 
-- Grouping by PK lets us select u.full_name / u.email without listing them
SELECT u.id, u.full_name, u.email, count(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id
ORDER BY orders DESC, u.id;

WHERE vs HAVING

Aspect	WHERE	HAVING
Runs	Before grouping	After grouping
Filters	Rows	Groups
Aggregates Allowed	No	Yes
Performance	Reduces rows early — put non-aggregate filters here	Only for aggregate conditions

-- Customers with 2+ non-cancelled orders and over 1000 spent
SELECT o.user_id, count(DISTINCT o.id) AS orders, sum(oi.quantity * oi.unit_price) AS spent
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'                       -- row filter
GROUP BY o.user_id
HAVING count(DISTINCT o.id) >= 2                    -- group filter
   AND sum(oi.quantity * oi.unit_price) > 1000
ORDER BY spent DESC;

Common Aggregate Functions

Function	Returns	Notes
*count()**	Rows in group	Counts NULL rows too
count(col)	Non-NULL values	`count(DISTINCT col)` for unique values
sum / avg	Total / mean	NULL for empty input; `avg(int)` returns `numeric`
min / max	Extremes	Work on text, dates too
bool_and / bool_or	All / any true	`every()` is the standard alias of `bool_and`
string_agg(expr, sep ORDER BY …)	Concatenated text	Order inside the call
array_agg(expr ORDER BY …)	Array	Includes NULLs
jsonb_agg / jsonb_object_agg	JSON array / object	Build API payloads in SQL
percentile_cont(0.5) WITHIN GROUP (ORDER BY x)	Median (interpolated)	Ordered-set aggregate
mode() WITHIN GROUP (ORDER BY x)	Most frequent value

SELECT c.name                                               AS category,
       count(*)                                             AS products,
       round(avg(p.price), 2)                               AS avg_price,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY p.price) AS median_price,
       bool_and(p.stock > 0)                                AS all_in_stock,
       string_agg(p.name, ', ' ORDER BY p.price DESC)       AS products_by_price,
       array_agg(p.sku ORDER BY p.sku)                      AS skus
FROM products p
JOIN categories c ON c.id = p.category_id
GROUP BY c.name
ORDER BY c.name;

Nesting Rows as JSON

-- One row per order with its items as a JSON array — handy for APIs
SELECT o.id,
       o.status,
       jsonb_agg(jsonb_build_object('sku', p.sku, 'qty', oi.quantity) ORDER BY p.sku) AS items
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
WHERE o.id IN (1, 8)
GROUP BY o.id
ORDER BY o.id;

FILTER Clause (Conditional Aggregation)

Why

Pivot-style Reports: Many conditional counts/sums in one pass over the data
Cleaner Than CASE: count(*) FILTER (WHERE …) vs sum(CASE WHEN … THEN 1 ELSE 0 END)
Standard SQL: Part of SQL:2003, supported in Postgres since 9.4 (MySQL lacks it)

SELECT date_trunc('month', created_at)::date                AS month,
       count(*)                                              AS all_orders,
       count(*) FILTER (WHERE status = 'delivered')          AS delivered,
       count(*) FILTER (WHERE status IN ('pending','paid'))  AS open,
       count(*) FILTER (WHERE status = 'cancelled')          AS cancelled,
       -- the portable equivalent:
       sum(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_case
FROM orders
GROUP BY 1
ORDER BY 1;
 
-- Payment method mix per order status
SELECT o.status,
       sum(p.amount) FILTER (WHERE p.method = 'card')   AS card,
       sum(p.amount) FILTER (WHERE p.method = 'upi')    AS upi,
       sum(p.amount) FILTER (WHERE p.method = 'wallet') AS wallet
FROM orders o
JOIN payments p ON p.order_id = o.id AND p.status = 'succeeded'
GROUP BY o.status
ORDER BY o.status;

GROUPING SETS, ROLLUP and CUBE

Concept

GROUPING SETS: Several GROUP BYs in one query, results stacked (like UNION ALL of each grouping, but one scan)
ROLLUP (a, b): Hierarchical subtotals → (a, b), (a), () — e.g. year → month → grand total
CUBE (a, b): Every combination → (a, b), (a), (b), ()
GROUPING(col): Returns 1 when col is rolled up in that row — distinguishes a subtotal NULL from a real NULL

Clause	Groupings for (country, status)	Rows Produced
GROUP BY country, status	(country, status)	Detail only
ROLLUP (country, status)	(country, status), (country), ()	Detail + per-country + total
CUBE (country, status)	(country, status), (country), (status), ()	All subtotals
GROUPING SETS ((country), (status))	(country), (status)	Only what you list

-- Revenue by country and status with subtotals and a grand total
SELECT CASE WHEN GROUPING(u.country) = 1 THEN 'ALL' ELSE COALESCE(u.country, '??') END AS country,
       CASE WHEN GROUPING(o.status)  = 1 THEN 'ALL' ELSE o.status END                  AS status,
       sum(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN users u        ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY ROLLUP (u.country, o.status)
ORDER BY GROUPING(u.country), u.country, GROUPING(o.status), o.status;

-- Two independent breakdowns in one query
SELECT method, status, count(*) AS payments, sum(amount) AS amount
FROM payments
GROUP BY GROUPING SETS ((method), (status), ())
ORDER BY method NULLS LAST, status NULLS LAST;

-- Every combination of method × status
SELECT method, status, count(*)
FROM payments
GROUP BY CUBE (method, status)
ORDER BY method NULLS LAST, status NULLS LAST;

string_agg and array_agg in Practice

-- Comma-separated product list per order
SELECT oi.order_id, string_agg(p.name, ', ' ORDER BY p.name) AS products
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY oi.order_id
HAVING count(*) > 1
ORDER BY oi.order_id;
 
-- array_agg + unnest round trip; DISTINCT inside the aggregate
SELECT user_id,
       array_agg(DISTINCT status ORDER BY status) AS statuses,
       array_length(array_agg(id), 1)             AS order_count
FROM orders
GROUP BY user_id
ORDER BY user_id;

Common Mistakes

Fan-out Double Counting

Problem: Joining orders → order_items and orders → payments multiplies rows (items × payments), inflating sums
Fix: Aggregate each child table in its own subquery/CTE, then join the per-order results

-- WRONG: order 8 has 3 items and 2 payments → 6 joined rows → payments counted 3×
SELECT o.id, sum(p.amount) AS paid_wrong
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN payments p     ON p.order_id = o.id AND p.status = 'succeeded'
WHERE o.id = 8
GROUP BY o.id;                                      -- 3399.00
 
-- RIGHT: pre-aggregate
SELECT o.id, i.due, p.paid
FROM orders o
JOIN (SELECT order_id, sum(quantity * unit_price) AS due FROM order_items GROUP BY order_id) i ON i.order_id = o.id
JOIN (SELECT order_id, sum(amount) AS paid FROM payments WHERE status = 'succeeded' GROUP BY order_id) p ON p.order_id = o.id
WHERE o.id = 8;                                     -- 1133.00 / 1133.00

Other Gotchas

count(*) with LEFT JOIN: Counts the NULL-extended row as 1 — use count(child.id)
sum Returns NULL on No Rows: Wrap with COALESCE(sum(x), 0)
avg of Integers: Returns numeric in Postgres (good), but integer division 5/2 = 2 elsewhere in expressions
Aggregates in WHERE: Not allowed — use HAVING or a subquery

Interview Questions

“WHERE vs HAVING?”: Row filter before grouping vs group filter after aggregation
“Count orders per status in one row?”: count(*) FILTER (WHERE status = …) (or sum(CASE …) for portability)
“Subtotals and grand total?”: ROLLUP; use GROUPING() to label total rows
“Why is my revenue doubled after adding a join?”: Fan-out — pre-aggregate per table
“Median in Postgres?”: percentile_cont(0.5) WITHIN GROUP (ORDER BY x)

Summary

GROUP BY + Aggregates: One output row per group; group by the PK to select dependent columns freely
HAVING: Filters on aggregate results; keep row filters in WHERE
FILTER: Clean conditional aggregation — the go-to for pivot reports
GROUPING SETS / ROLLUP / CUBE: Multiple levels of subtotals in one scan; GROUPING() marks subtotal rows
Pitfalls: Fan-out double counting, count(*) on outer joins, sum() of nothing is NULL

Kaushik's Notes

Explorer

Aggregation and GROUP BY

Overview

GROUP BY Basics

Rules

WHERE vs HAVING

Common Aggregate Functions

Nesting Rows as JSON

FILTER Clause (Conditional Aggregation)

Why

GROUPING SETS, ROLLUP and CUBE

Concept

string_agg and array_agg in Practice

Common Mistakes

Fan-out Double Counting

Other Gotchas

Interview Questions

Summary

Graph View

Table of Contents

Backlinks