Aggregation

COUNT, GROUP BY, HAVING, CASE

What aggregate functions are there?

An aggregate function reduces many rows to one value:

COUNT: the count
SUM: the sum
AVG: the average
MIN, MAX: the minimum and maximum (work with dates and strings too)

SELECT COUNT(*)    AS total,
       SUM(amount) AS volume,
       AVG(amount) AS avg_amount,
       MAX(created_at) AS last_tx
FROM transactions
WHERE status = 'completed';

In an interview: Aggregate functions reduce many rows to one value: COUNT counts the number, SUM the total, AVG the average, MIN and MAX the extreme values, and not only for numbers but also for dates. For example, MAX of the creation date quickly shows when the last transaction was.

How do COUNT(*), COUNT(col) and COUNT(DISTINCT col) differ?

COUNT(*) is the number of rows, including those with NULL.
COUNT(phone) is the number of rows where phone is not NULL.
COUNT(DISTINCT user_id) is the number of unique non-empty values.

If a table has 100 users and 30 without a phone: COUNT(*) = 100, COUNT(phone) = 70. The difference between them is a quick way to count empty values.

SELECT COUNT(*)                AS users_total,
       COUNT(phone)            AS with_phone,
       COUNT(*) - COUNT(phone) AS without_phone
FROM users;

In an interview: COUNT star counts all rows, COUNT of a column only those where the value is not NULL, and COUNT DISTINCT the number of unique non-empty values. The difference between the first two immediately shows how many records have an empty field, and I often use this to check data completeness after a migration.

How does GROUP BY work?

GROUP BY splits rows into groups with equal values and computes an aggregate for each group separately.

The rule: every column in SELECT must be either in GROUP BY or inside an aggregate function. Otherwise it is unclear which of the group's many values to show, and the database will return an error.

-- the number and volume of transactions by type and status
SELECT type, status, COUNT(*) AS cnt, SUM(amount) AS volume
FROM transactions
GROUP BY type, status
ORDER BY type, status;

-- error: email is neither in GROUP BY nor in an aggregate
SELECT country, email, COUNT(*) FROM users GROUP BY country;

In an interview: GROUP BY splits rows into groups with equal values and computes an aggregate for each group. The rule is simple: every column in SELECT must be either in GROUP BY or in an aggregate function. I often group by status to see the distribution of data and notice anomalies, for example an unexpectedly large number of failed.

How does WHERE differ from HAVING?

WHERE filters rows before grouping. Aggregates can't be used in it.
HAVING filters groups after grouping, by the results of aggregates.

If a condition can go into WHERE, it goes there: fewer rows go into grouping, and the query is faster.

-- users who have more than 3 failed transactions in September
SELECT a.user_id, COUNT(*) AS failed_cnt
FROM transactions t
JOIN accounts a ON a.id = t.account_id
WHERE t.status = 'failed'                      -- row filter
  AND t.created_at >= '2026-09-01'
  AND t.created_at <  '2026-10-01'
GROUP BY a.user_id
HAVING COUNT(*) > 3;                           -- group filter

In an interview: WHERE filters individual rows before grouping, and HAVING filters ready groups by the results of aggregates. So a condition like "the count is more than three" can only be written in HAVING. And everything that can be filtered before grouping I put in WHERE, so the query processes less data.

How do aggregates behave with NULL? What is COALESCE?

SUM, AVG, MIN, MAX ignore NULL. AVG of (100, NULL, 200) is 150, not 100.
An aggregate over an empty set of rows returns NULL, not 0. The exception is COUNT, which returns 0.

COALESCE(a, b, …) returns the first value that is not NULL. It is used to substitute default values.

-- the sum of deposits of a user without transactions is NULL, not 0
SELECT SUM(amount) FROM transactions WHERE account_id = 999;          -- NULL
SELECT COALESCE(SUM(amount), 0) FROM transactions WHERE account_id = 999; -- 0

In an interview: Aggregate functions ignore NULL, so the average is computed only over filled values. And a sum over an empty set of rows returns not zero but NULL, and if the code doesn't expect that, an empty field or an error will appear on screen. To turn NULL into a default value I use COALESCE.

How do you use CASE WHEN and conditional aggregation?

CASE is conditional logic in a query, the equivalent of if/else. It is especially useful together with aggregates: you can compute several indicators in one pass.

SELECT
  type,
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'failed') AS failed,        -- PostgreSQL
  SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_volume,
  ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'failed') / COUNT(*), 1) AS failed_pct
FROM transactions
GROUP BY type;

-- amount categories
SELECT id,
  CASE WHEN amount < 100  THEN 'small'
       WHEN amount < 1000 THEN 'medium'
       ELSE 'large' END AS bucket
FROM transactions;

In an interview: CASE WHEN is conditional logic in a query, like if-else. It is most useful to combine it with aggregates: in one query I can compute the total count, the number of failed ones and their percentage for each transaction type. Note the 100.0: without the fractional part, integer division would give zero.

WHERE vs HAVING and grouping

WHERE filters individual rows before grouping, so aggregate functions can't be used in it. HAVING filters the already formed groups after GROUP BY and works with aggregates: COUNT, SUM, AVG. The order of execution: FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.

A query: the number of orders per user, only where more than 5:

SELECT user_id, COUNT(*) AS orders_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY orders_count DESC;

In a SELECT with GROUP BY you may list only grouping fields and aggregates. If you also need the user's name, I add a JOIN with users and group by id and name. A filter such as "only paid orders" I put in WHERE, so it works before grouping and is more efficient.