SQL GROUP BY examples

The core patterns

-- how many orders per customer?
SELECT user_id, COUNT(*) AS orders
FROM orders
GROUP BY user_id;

-- revenue per customer, biggest first, top 10
SELECT user_id, SUM(amount) AS revenue
FROM orders
GROUP BY user_id
ORDER BY revenue DESC
LIMIT 10;

-- several aggregates at once (the report query)
SELECT user_id,
       COUNT(*)    AS orders,
       SUM(amount) AS revenue,
       AVG(amount) AS avg_order,
       MAX(amount) AS biggest
FROM orders
GROUP BY user_id;

-- group by a computed value: orders per month
SELECT strftime('%Y-%m', created_at) AS month,   -- SQLite;
       COUNT(*)                                    -- Postgres: date_trunc
FROM orders
GROUP BY month;

THE error

SELECT user_id, name, SUM(amount)   -- ← name is the problem
FROM orders JOIN users ON ...
GROUP BY user_id;

ERROR: column "users.name" must appear in the GROUP BY
clause or be used in an aggregate function

Why: after collapsing, a bucket of 50 rows has 50 name values — the database refuses to pick one for you. Fix: either add it to the bucket key (GROUP BY user_id, name — fine, since name is constant per user), or wrap it in an aggregate (MAX(name) — the "I know it's constant" shrug).

Filtering groups: HAVING

-- customers who spent 100+ (condition on the BUCKET → HAVING)
SELECT user_id, SUM(amount) AS total
FROM orders
WHERE created_at >= '2026-01-01'    -- row filter: BEFORE grouping
GROUP BY user_id
HAVING SUM(amount) >= 100;          -- bucket filter: AFTER

-- the tell: condition contains SUM/COUNT → it can't go in WHERE

The duplicate finder (memorize this one)

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- every duplicated email, with its count. works for any column.