GROUP BY and Aggregation

TL;DR

GROUP BY collapses rows that share the same key into a single output row. SUM, COUNT, AVG, MIN, MAX (and friends) compute summary values within each group. Together they answer the question every analyst writes a hundred times: “X per Y” — revenue per customer, errors per service, predictions per model version.

The one rule that catches every beginner: every column in SELECT must either appear in GROUP BY or be wrapped in an aggregate. Postgres and ANSI SQL enforce this strictly. (MySQL historically didn’t, which is why MySQL queries copied to Postgres so often error with “column must appear in GROUP BY”.)

A worked example

CREATE TABLE orders (
    id      int PRIMARY KEY,
    user_id int,
    country text,
    total   numeric,
    placed  date
);

INSERT INTO orders VALUES
  (1, 1, 'US', 19.99, '2026-04-01'),
  (2, 1, 'US', 42.00, '2026-04-15'),
  (3, 2, 'US',  7.50, '2026-04-20'),
  (4, 3, 'UK', 99.00, '2026-04-22'),
  (5, 3, 'UK', 12.00, '2026-04-25'),
  (6, 4, 'IN',  5.00, '2026-04-28'),
  (7, 5, 'UK', NULL,  '2026-04-29');

“Total spend per country”:

SELECT country,
       COUNT(*)        AS n_orders,
       SUM(total)      AS revenue,
       AVG(total)      AS avg_order,
       MIN(total)      AS min_order,
       MAX(total)      AS max_order
FROM   orders
GROUP  BY country
ORDER  BY revenue DESC NULLS LAST;

+---------+----------+---------+-----------+-----------+-----------+
| country | n_orders | revenue | avg_order | min_order | max_order |
+---------+----------+---------+-----------+-----------+-----------+
| UK      |    3     | 111.00  |  55.50    |   12.00   |   99.00   |
| US      |    3     |  69.49  |  23.16    |    7.50   |   42.00   |
| IN      |    1     |   5.00  |   5.00    |    5.00   |    5.00   |
+---------+----------+---------+-----------+-----------+-----------+

UK has 3 orders summing to 111 — note: row 7 has total = NULL and it was silently dropped from SUM and AVG. That’s a feature, not a bug, but worth knowing (see below).

The aggregate functions worth memorizing

COUNT(*) vs COUNT(col) is the most common subtle bug. If you want to count rows, use *. If you want to count “rows where this column has a value”, use COUNT(col).

The GROUP BY rule, in one sentence

Every non-aggregated column in SELECT must appear in GROUP BY.

Why: the engine produces one row per group. If you ask for country and SUM(total) per country, that’s well-defined — one country, one sum. If you also ask for placed without grouping by it, which date does it pick? There’s no good answer, so SQL says “no”.

-- ERROR: column "placed" must appear in the GROUP BY clause
SELECT country, placed, SUM(total)
FROM   orders
GROUP  BY country;

Fix: either group by placed too (gives one row per country-date), or aggregate it (MAX(placed), MIN(placed)):

SELECT country, MAX(placed) AS last_order, SUM(total) AS revenue
FROM   orders
GROUP  BY country;

MySQL note. Old MySQL versions allowed bare columns in SELECT without grouping; it returned an arbitrary value from the group. This caused a generation of subtle bugs. Modern MySQL with ONLY_FULL_GROUP_BY behaves like Postgres. Don’t rely on the old behavior.

Grouping by multiple columns

SELECT country, EXTRACT(WEEK FROM placed) AS wk, SUM(total)
FROM   orders
GROUP  BY country, EXTRACT(WEEK FROM placed)
ORDER  BY country, wk;

One row per (country, week) pair. Use this for cohort tables, daily/weekly breakdowns, segment × period analysis.

You can also group by position (GROUP BY 1, 2) or by SELECT alias in some dialects (Postgres allows it; standard SQL does not). Position is fine for ad-hoc queries; brittle in production.

Filtering inside aggregates: FILTER and CASE

A pattern that quietly replaces 80% of subqueries: aggregate only certain rows.

-- Postgres / Snowflake / DuckDB: FILTER clause
SELECT country,
       COUNT(*)                                         AS total_orders,
       COUNT(*) FILTER (WHERE total > 50)               AS big_orders,
       SUM(total) FILTER (WHERE placed >= '2026-04-20') AS late_revenue
FROM   orders
GROUP  BY country;

+---------+--------------+------------+--------------+
| country | total_orders | big_orders | late_revenue |
+---------+--------------+------------+--------------+
| US      |      3       |     0      |     7.50     |
| UK      |      3       |     1      |   111.00     |
| IN      |      1       |     0      |     5.00     |
+---------+--------------+------------+--------------+

FILTER (WHERE ...) is cleaner than the older SUM(CASE WHEN cond THEN x ELSE 0 END) idiom, but the CASE form works in every dialect:

-- Portable equivalent
SELECT country,
       SUM(CASE WHEN total > 50 THEN 1 ELSE 0 END) AS big_orders
FROM   orders
GROUP  BY country;

This is the fundamental conditional-aggregation trick. Use it for ratios, funnel conversion rates, and “X out of Y” features.

ROLLUP, CUBE, GROUPING SETS

When you want subtotals at multiple levels of the hierarchy.

SELECT country,
       EXTRACT(MONTH FROM placed) AS mo,
       SUM(total) AS revenue
FROM   orders
GROUP  BY ROLLUP (country, mo);

ROLLUP(a, b) produces groups for (a, b), (a), and () (the grand total). CUBE(a, b) adds (b) too — every subset. GROUPING SETS lets you list arbitrary combinations.

These exist in Postgres, Snowflake, BigQuery, and most modern DBs. They replace the old “UNION ALL of subtotals” pattern. Use sparingly — they’re a hint that what you actually want is a BI tool.

Common pitfalls

• COUNT(NULL) is 0, not NULL. COUNT(col) over an all-NULL group returns 0; SUM(col) over an all-NULL group returns NULL. Easy to get wrong when filling missing data.
• AVG over a column with NULLs is NOT total/row-count. It’s SUM(non-null) / COUNT(non-null). If you want NULLs treated as zero, use AVG(COALESCE(col, 0)) or SUM(col) / COUNT(*).
• Joining before grouping inflates aggregates. Joining orders to order_items and then SUM(orders.total) over-counts because each order’s total appears once per item. Pre-aggregate one side.
• GROUP BY on a computed expression you forgot to also put in SELECT. Confusing but legal — common with date truncation: GROUP BY DATE_TRUNC('day', placed) without selecting the truncated date back out.
• Forgetting that HAVING exists. Filtering aggregated results with WHERE errors out (WHERE runs before grouping). Use HAVING. See SQL 104.

Production patterns for ML

1. The “feature aggregations” template. Almost every per-user feature is an aggregation over a window:

SELECT user_id,
       COUNT(*)                                            AS orders_30d,
       SUM(total)                                          AS spend_30d,
       AVG(total)                                          AS avg_basket_30d,
       SUM(total) FILTER (WHERE category = 'electronics')  AS electronics_30d,
       MAX(placed)                                         AS last_order_30d
FROM   orders
WHERE  placed >= CURRENT_DATE - INTERVAL '30 days'
GROUP  BY user_id;

Run the same template with 7-day, 30-day, 90-day windows to get a multi-horizon feature set. This pattern alone produces hundreds of features with almost no code.

2. The “funnel” template. Conditional aggregation over event types:

SELECT user_id,
       COUNT(*) FILTER (WHERE event = 'view')     AS views,
       COUNT(*) FILTER (WHERE event = 'add_cart') AS adds,
       COUNT(*) FILTER (WHERE event = 'purchase') AS purchases,
       1.0 * COUNT(*) FILTER (WHERE event = 'purchase')
           / NULLIF(COUNT(*) FILTER (WHERE event = 'view'), 0) AS conv_rate
FROM   events
WHERE  ts >= CURRENT_DATE - INTERVAL '7 days'
GROUP  BY user_id;

NULLIF(x, 0) avoids a divide-by-zero error and produces NULL instead — which AVG and downstream nicely handle.

Practice problems

Resources

• PostgreSQL — aggregate functions — postgresql.org/docs
• Mode Analytics — GROUP BY — mode.com/sql-tutorial
• Use The Index, Luke — use-the-index-luke.com — when grouping is slow, indexing is usually why.
• PostgreSQL documentation — postgresql.org/docs