GROUP BY & aggregates
In one line
GROUP BY collapses rows that share the same key into one row per group, aggregates like COUNT and SUM summarise each group, and HAVING filters the groups.
What it is
Aggregate functions turn many rows into one value: COUNT, SUM, AVG, MIN and MAX are the core five. Without GROUP BY they summarise the whole table into a single row. With GROUP BY dept they produce one row per distinct dept.
After grouping, each output row represents a whole group, so the SELECT list may only contain the grouping columns, aggregates, or expressions built from them. Selecting a plain column that varies inside the group (like name when grouping by dept) is an error in standard SQL and PostgreSQL.
HAVING is a filter on groups, applied after aggregation, so it can use aggregates: HAVING COUNT(*) > 5. WHERE is applied before grouping and cannot.
Why interviewers ask
Most business questions are aggregations: revenue per month, users per country, the top customers by spend. Interviewers check WHERE vs HAVING, how aggregates treat NULL, COUNT(*) vs COUNT(column), and whether you can do conditional aggregation in one pass instead of several queries.
How it works
- 1FROM and WHERE produce the input rows. Filtering here is cheapest because fewer rows reach the grouping step.
- 2GROUP BY puts rows with equal values in the grouping columns into the same bucket. NULLs in a grouping column form their own single group.
- 3Each aggregate is computed per bucket. COUNT(*) counts rows; COUNT(col) counts non-NULL values of col; COUNT(DISTINCT col) counts distinct non-NULL values. SUM, AVG, MIN and MAX ignore NULLs.
- 4HAVING removes buckets whose condition is not TRUE.
- 5SELECT outputs one row per remaining bucket, then ORDER BY and LIMIT apply as usual.
- 6Internally the database groups either by hashing the key (HashAggregate in PostgreSQL) or by sorting rows on the key and scanning runs of equal values (GroupAggregate).
Worked example · Paid revenue per customer, only customers with at least 40
- orders(id, customer, status, amount): (1, Ana, paid, 50), (2, Ana, paid, 30), (3, Raj, refunded, 20), (4, Raj, paid, 40), (5, Mei, paid, 25).
- Query: SELECT customer, COUNT(*) AS n, SUM(amount) AS total FROM orders WHERE status = 'paid' GROUP BY customer HAVING SUM(amount) >= 40 ORDER BY total DESC.
- WHERE drops order 3 (refunded). 4 rows remain: Ana 50, Ana 30, Raj 40, Mei 25.
- GROUP BY customer makes 3 buckets: Ana {50, 30}, Raj {40}, Mei {25}.
- Aggregates: Ana n = 2, total = 80. Raj n = 1, total = 40. Mei n = 1, total = 25.
- HAVING SUM(amount) >= 40 drops Mei (25). ORDER BY total DESC gives (Ana, 2, 80), (Raj, 1, 40).
- If the refund filter had been in HAVING instead of WHERE it would not even be valid, because status is not grouped. Row filters belong in WHERE.
Picture it
Sorting a pile of receipts into envelopes by shop name is GROUP BY. Writing the count and total on the front of each envelope is the aggregate. Throwing away envelopes whose total is under 40 is HAVING. Throwing away refund receipts before you start sorting is WHERE.
Reference code
-- orders(id, customer, status, amount)
SELECT customer,
COUNT(*) AS n,
SUM(amount) AS total
FROM orders
WHERE status = 'paid' -- row filter: before grouping
GROUP BY customer
HAVING SUM(amount) >= 40 -- group filter: after grouping
ORDER BY total DESC;
-- One pass, several conditional totals
SELECT customer,
SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid,
SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded,
COUNT(*) FILTER (WHERE status = 'refunded') AS refunds -- PostgreSQL only
FROM orders
GROUP BY customer;The first query returns (Ana, 2, 80) and (Raj, 1, 40) on the example table. The second returns one row per customer: Ana paid 80 refunded 0, Raj paid 40 refunded 20 with 1 refund, Mei paid 25. In MySQL replace the FILTER line with SUM(status = 'refunded').
Key patterns
Conditional aggregation (pivot)
When: Several counts or sums with different conditions in one pass
How: SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END). PostgreSQL also supports SUM(amount) FILTER (WHERE status = 'paid'). In MySQL a boolean is 0 or 1, so SUM(status = 'paid') counts matches.
Find duplicates
When: Before adding a unique index or cleaning data
How: GROUP BY the candidate key HAVING COUNT(*) > 1.
Top group
When: The department with the highest payroll
How: GROUP BY dept ORDER BY SUM(salary) DESC LIMIT 1, or use RANK() over the aggregated result to keep ties.
Subtotals
When: Totals per group plus a grand total
How: GROUP BY ROLLUP (country, city) in PostgreSQL (also GROUPING SETS and CUBE); MySQL writes GROUP BY country, city WITH ROLLUP.
Real-world use
Product analytics
Tools like Metabase or Looker turn every chart into a GROUP BY: daily active users is COUNT(DISTINCT user_id) grouped by date, run against PostgreSQL, BigQuery or Snowflake.
Finance reports
Monthly revenue reports group payments by date_trunc('month', paid_at) (PostgreSQL) and use conditional sums to split revenue by plan or country in one query.
Fraud and abuse detection
A payments team finds cards used from more than 3 countries in a day with GROUP BY card_id, day HAVING COUNT(DISTINCT country) > 3.
Data quality checks
Finding duplicates before adding a unique constraint: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1.
In a project
A food delivery startup wants a dashboard tile showing, per city per week, the number of orders, revenue, average order value and the share of orders that were late.
- Filter to completed orders in the last 12 weeks in WHERE, so the aggregation only touches relevant rows.
- GROUP BY city, date_trunc('week', created_at) to get one row per city per week.
- Compute COUNT(*), SUM(total), AVG(total) and the late share with conditional aggregation: AVG(CASE WHEN delivered_at > promised_at THEN 1.0 ELSE 0 END).
- Use HAVING COUNT(*) >= 30 so tiny cities with noisy percentages are hidden.
- Precompute the result nightly into a summary table so the dashboard reads a few hundred rows instead of millions.
Impact: One query replaced four separate ones, the dashboard loads in under a second from the summary table, and operations managers could see that late deliveries in two cities doubled in the same week a new courier policy launched.
Trade-offs
Use it when
- You need one summary row per category, period or entity
- Counting distinct values (unique users, unique products)
- Conditional aggregation can replace several separate queries over the same table
Avoid it when
- You need the detail rows alongside the summary (each order next to its customer's total): use a window function instead
- Grouping by a high-cardinality column like id just to get one row per row
- Putting row-level filters in HAVING; they belong in WHERE where they run earlier and cheaper
Cost: Hash aggregation is about O(n) time with memory for one entry per group; sort-based aggregation is O(n log n) unless an index already supplies rows in key order. COUNT(DISTINCT) needs extra memory or a sort for the distinct values.
Common mistakes
- Selecting a non-grouped, non-aggregated column. PostgreSQL rejects it (unless it depends on a grouped primary key); MySQL with ONLY_FULL_GROUP_BY (the default since 5.7) also rejects it, while older MySQL returned an arbitrary value.
- Using WHERE COUNT(*) > 1. Aggregates are not allowed in WHERE; use HAVING.
- Expecting COUNT(col) to count all rows: it skips NULLs. COUNT(*) counts rows.
- Forgetting that AVG ignores NULLs. AVG of 10, NULL, 20 is 15, not 10. Use AVG(COALESCE(x, 0)) if missing should mean zero.
- Forgetting that SUM over zero rows returns NULL, not 0. Wrap it in COALESCE(SUM(x), 0) when needed.
Interview questions
Quick revision
- GROUP BY: one output row per distinct key. NULL keys form one group.
- SELECT only grouped columns or aggregates.
- WHERE before grouping (rows); HAVING after grouping (aggregates).
- COUNT(*) counts rows; COUNT(col) skips NULLs; COUNT(DISTINCT col) counts unique non-NULL values.
- SUM, AVG, MIN, MAX ignore NULLs; SUM of no rows is NULL.
- Conditional aggregation: SUM(CASE WHEN ... THEN 1 ELSE 0 END) or FILTER (WHERE ...) in PostgreSQL.
- ROLLUP / GROUPING SETS for subtotals; MySQL uses WITH ROLLUP.
Check yourself
1. A column has 10 rows, 2 of them NULL. What do COUNT(*) and COUNT(column) return?
2. Where do you filter to keep only customers whose total spend is over 100?
3. What is AVG over the values 10, NULL and 20?
4. What happens in PostgreSQL with SELECT dept, name, COUNT(*) FROM employees GROUP BY dept?
Saved on this device. Create a free account to keep it on every device.
Practise this with Sam
A real mock interview, out loud, with an honest scorecard. Free to start.