SQL Aggregate Functions Interview Questions and Answers

Aggregate functions look like the easiest SQL topic, which is exactly why interviewers use them to catch careless candidates. The questions almost always hinge on NULL handling and on the WHERE-versus-HAVING distinction — two things that separate someone who has only run simple COUNT(*) queries from someone who understands how aggregation really works. Analysts, backend developers and data engineers all face these.

The examples use orders(id, customer_id, status, amount, shipped_at) and standard SQL that runs on PostgreSQL, MySQL, SQL Server and Oracle.

What is an aggregate function?

An aggregate function takes many rows and returns a single summary value: COUNT, SUM, AVG, MIN, MAX are the core five. With GROUP BY it returns one value per group; without it, one value for the whole result.

The behaviour to anchor on: every standard aggregate except COUNT(*) ignores NULLs. State that once and most of the tricky questions below answer themselves.

SELECT COUNT(*) AS orders, SUM(amount) AS revenue, AVG(amount) AS avg_order
FROM orders;

Q1. COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column)?

COUNT(*) counts every row. COUNT(column) counts rows where the column is not NULL. COUNT(DISTINCT column) counts distinct non-null values. They diverge exactly by NULLs and duplicates.

SELECT
  COUNT(*)                    AS all_rows,
  COUNT(shipped_at)           AS shipped_rows,     -- excludes unshipped (NULL) orders
  COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

The classic trap: someone uses COUNT(shipped_at) expecting a total row count and silently drops unshipped orders. Explaining that the difference equals the number of NULLs in that column proves you understand the mechanism.

Interview note: Trap: "is COUNT(1) different from COUNT(*)?" No — both count all rows; the 1 is a constant that is never NULL. Any performance difference is a myth on modern engines.

Q2. How do SUM, AVG, MIN and MAX handle NULLs?

They ignore NULLs entirely. SUM adds only non-null values, AVG divides the non-null sum by the non-null count, and MIN/MAX consider only non-null values.

-- If some amounts are NULL, AVG divides by the count of non-null amounts
SELECT AVG(amount) FROM orders;

-- Treat NULL as zero explicitly
SELECT AVG(COALESCE(amount, 0)) FROM orders;

The AVG case is the one that bites: AVG(amount) and SUM(amount) / COUNT(*) give different answers when NULLs exist, because AVG's denominator excludes them. If business rules say "missing = 0", you must COALESCE first — the query won't do it for you.

Interview note: Follow-up: "what does SUM return over all-NULL or zero rows?" NULL, not 0. An empty set summed is NULL — guard reports with COALESCE(SUM(x), 0).

Q3. WHERE vs HAVING — when do you use each?

WHERE filters rows before grouping and cannot use aggregates. HAVING filters groups after aggregation and can use aggregates. Filter with WHERE as early as possible; use HAVING only for conditions on aggregated values.

SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE status = 'PAID'         -- row filter, before grouping
GROUP BY customer_id
HAVING SUM(amount) > 10000;   -- group filter, after aggregation

The efficiency point matters: pushing conditions into WHERE shrinks the set before the expensive grouping, so putting a non-aggregate condition in HAVING is both wrong-headed and slower. Interviewers watch for candidates who misuse HAVING for row filtering.

Interview note: Trap: "can WHERE reference AVG(amount)?" No — aggregates don't exist yet at the WHERE stage. Any aggregate condition must go in HAVING.

Q4. What is conditional aggregation and why is it powerful?

Conditional aggregation nests a CASE inside an aggregate so you total or count only rows meeting a condition. It computes several conditional metrics in one scan and is how you pivot rows into columns.

SELECT customer_id,
  SUM(CASE WHEN status = 'PAID'     THEN amount ELSE 0 END) AS paid_total,
  SUM(CASE WHEN status = 'REFUNDED' THEN amount ELSE 0 END) AS refunded_total,
  COUNT(CASE WHEN status = 'PAID'   THEN 1 END)             AS paid_count
FROM orders
GROUP BY customer_id;

This one technique replaces multiple separate queries or a self-join, and it is the SQL way to pivot without a dedicated PIVOT clause. Note that COUNT(CASE WHEN ... THEN 1 END) counts only matching rows because the ELSE is NULL and COUNT ignores NULLs — a neat use of Q2's rule.

Interview note: Follow-up: "SUM vs COUNT in conditional aggregation?" SUM(CASE WHEN cond THEN 1 ELSE 0 END) and COUNT(CASE WHEN cond THEN 1 END) both count matches; the SUM form is more explicit about the ELSE.

Q5. Can you nest aggregate functions?

Not directly — MAX(AVG(amount)) is illegal in a single level. You compute the inner aggregate in a subquery or CTE, then aggregate its result in the outer query.

-- Highest per-customer average order value
SELECT MAX(avg_amount) AS top_customer_avg
FROM (
  SELECT customer_id, AVG(amount) AS avg_amount
  FROM orders GROUP BY customer_id
) per_customer;

The reason is that an aggregate operates on rows, and the result of an inner aggregate isn't available as rows until you materialize it with grouping. This connects aggregates to subqueries — a common cross-topic question.

Interview note: Trap: "does a window function let you skip the subquery?" For some cases yes — MAX(AVG(amount)) OVER () still needs grouping first, but window functions can layer over aggregated results in one query level.

Q6. What is the difference between AVG and a manually computed mean?

AVG(column) equals SUM(column)/COUNT(column) — both over non-null values. It differs from SUM(column)/COUNT() whenever NULLs exist, because COUNT() includes NULL rows in the denominator.

-- These match only when 'amount' has no NULLs:
SELECT AVG(amount) AS builtin_avg,
       SUM(amount) / COUNT(*) AS wrong_when_nulls,
       SUM(amount) / COUNT(amount) AS matches_avg
FROM orders;

This question is a precise test of Q2 understanding. Being able to write the exact equivalent of AVG — and name why the naive division is wrong with NULLs — is a strong signal.

Interview note: Follow-up: "integer division gotcha?" In some engines SUM(int)/COUNT(int) does integer division and truncates. Cast to a decimal type to get a true mean.

Q7. How do you find the group with the maximum aggregate value?

Compute the aggregate per group, then either order and limit, or filter to the max in an outer query. A plain MAX() gives the value, not the group that owns it.

-- Customer with the highest total spend
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
ORDER BY total DESC
LIMIT 1;

The subtlety interviewers probe: SELECT customer_id, MAX(SUM(amount)) doesn't work, and grabbing MAX(total) alone loses which customer it belongs to. Ordering + LIMIT, or a window function ranking, is the correct pattern. Ties may need special handling.

Interview note: Trap: "what if two customers tie for the top?" LIMIT 1 arbitrarily drops one. Use RANK() in a window or a HAVING on the max to return all tied groups.

Q8. What does GROUPING SETS / ROLLUP add to aggregation?

They produce multiple aggregation levels in one query — subtotals and grand totals. ROLLUP gives hierarchical subtotals; GROUPING SETS lets you specify exactly which groupings you want.

-- Totals per status, plus an overall total row
SELECT status, SUM(amount) AS total
FROM orders
GROUP BY ROLLUP (status);

The ROLLUP(status) adds a summary row where status is NULL representing the grand total. This is how reports get subtotals without UNIONing several queries. Mentioning the GROUPING() function to distinguish a real NULL from a subtotal NULL is an advanced touch.

Interview note: Follow-up: "how do you tell a subtotal NULL from a data NULL?" The GROUPING(column) function returns 1 for the subtotal-generated NULL and 0 for an actual NULL value.

How to prepare

Create an orders table where some amount and shipped_at values are NULL, then run every COUNT variant and compare AVG against both SUM/COUNT(*) and SUM/COUNT(column) — seeing the numbers diverge burns the NULL rules into memory permanently. Then write a conditional-aggregation pivot that turns statuses into columns; that single query is one of the most reused patterns in analyst work.

Pair this with the GROUP BY and HAVING questions, which are the natural companion to aggregation, and the window functions set, where these same aggregates run over frames without collapsing rows. The SQL learning path covers the NULL three-valued logic underneath every answer here. A mock interview built around a reporting query is the closest rehearsal for the aggregate round.