SQL GROUP BY examples

Grouping turns a long event table into a report. Pick the fact that defines each bucket, then calculate a count, total, average, or maximum for every bucket. You can calculate several summaries at once; the database still returns one row per bucket.

-- 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 (SQLite)
SELECT strftime('%Y-%m', created_at) AS month, COUNT(*)
FROM orders GROUP BY month;

After rows collapse, every selected value needs one unambiguous answer per bucket. If fifty source rows could supply fifty different names, the database refuses to guess. Add the name to the bucket definition when it is part of the identity, or summarize it only when you genuinely know the values are equivalent.

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

-- concrete fix when one name belongs to each user:
GROUP BY user_id, name

Use a row filter before grouping and a bucket filter afterward. If your condition depends on a total or count, it belongs after the grouping step. This distinction also powers the duplicate finder: make one bucket per email, then keep only buckets containing more than one row.

-- customers who spent 100+ in 2026
SELECT user_id, SUM(amount) AS total
FROM orders
WHERE created_at >= '2026-01-01'    -- rows before grouping
GROUP BY user_id
HAVING SUM(amount) >= 100;          -- buckets after grouping

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