Chapter 2 · 8 min
Sums, averages, and extremes
sum, avg, min, and max answer different questions, and interview prompts often mix them in
ways that reward reading carefully. A total is not an average, an average is not a median, and an
extreme over a filtered set is not an extreme over the table.
SELECT
category,
sum(unit_price * quantity) AS revenue,
round(avg(unit_price), 2) AS average_price
FROM order_items
JOIN products USING (product_id)
GROUP BY category
ORDER BY revenue DESC;
sum adds every non-null value in the group. avg divides the sum by the number of non-null values,
not by the number of rows, so a column with missing entries produces a larger average than a naive
row count would suggest. min and max ignore nulls too; if every value in a group is null, the
aggregate returns null rather than zero.
Empty groups and missing values
An aggregate over zero rows returns null for sum, avg, min, and max, but count(*)
returns 0. That difference matters when a query is expected to return one row per group: a
customer with no orders has no group at all under an inner join, so the row disappears rather than
showing zero. Use an outer join and COALESCE when the report needs a zero.
SELECT c.customer_id, COALESCE(count(o.order_id), 0) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id;
Precision and rounding
Money columns are numeric, so sums stay exact until you divide. avg returns a numeric with more
digits than a report wants; round explicitly in the select list, and do not round before summing or
the totals drift. Casting to float introduces binary floating-point error, which is why the
grading rules for numeric answers compare within a tolerance instead of demanding string equality.
Counting distinct things
count(DISTINCT column) counts distinct non-null values. It is the right tool for “how many
different customers ordered this product”, and the wrong tool for “how many orders” — two orders
from the same customer are still two orders. The distinction between counting rows and counting
entities is one of the most common interview checks in this chapter.
Naming the output
Interview prompts usually specify column names. Alias every aggregate to the requested name, and keep the group key visible so the result can be checked row by row. A correct number under an unexpected column name reads as a wrong answer to both a human and an automated grader.