Detailed explanation
SQL aggregate functions summarise a set of rows into a single result per group. Common examples are COUNT, SUM, AVG, MIN and MAX: they count values, add them, calculate their average, or find the smallest and largest values. Without GROUP BY, an aggregate query treats the selected rows as one group. With GROUP BY department, it returns a separate summary for each department. These functions are useful for reports such as employee counts, total sales and average salaries.
NULL represents a missing, unknown or inapplicable value; it is not automatically zero or an empty string. For these five aggregates, expressions that evaluate to NULL are excluded from the calculation. The important exception is COUNT(*), which counts rows regardless of whether their columns contain NULL. COUNT(salary) counts only rows with a non-NULL salary. For salaries 100, NULL and 200, SELECT COUNT(*), COUNT(salary), AVG(salary) FROM employees returns 3, 2 and 150. SUM(salary) returns 300, MIN(salary) returns 100 and MAX(salary) returns 200.
Conceptually, an aggregate maintains the information needed as it processes qualifying values: a count, a running total, or the current minimum or maximum. AVG uses the sum and count of non-NULL values, so the missing salary does not increase its denominator. This distinction matters because AVG(COALESCE(salary, 0)) replaces missing salaries with zero before averaging and produces 100 for this example. That is a different business assumption, not merely an alternative way to write the same calculation. Replacing unknown data with zero can make a report misleading.
A key edge case is an input containing no non-NULL values. COUNT(salary) returns 0, while SUM, AVG, MIN and MAX return NULL. If WHERE filters out every row, an aggregate query without GROUP BY still returns one summary row: both COUNT forms return 0, and the other aggregates return NULL. A normal grouped query over no input rows returns no groups. Interviewers typically check whether you can distinguish counting rows from counting known values, explain AVG's denominator, and recognise that an empty sum is NULL rather than zero in SQL.
Key points to remember
- Aggregates return one summary value per group; without GROUP BY, the input forms one group.
- COUNT(*) counts rows, including rows containing NULLs.
- COUNT(col), SUM(col), AVG(col), MIN(col) and MAX(col) ignore NULL values.
- AVG(col) uses the non-NULL count as its denominator.
- With no qualifying values, COUNT returns 0; SUM, AVG, MIN and MAX return NULL.
Common mistakes candidates make
- Saying all aggregates ignore NULLs without explaining the COUNT(*) exception.
- Treating NULL as zero and dividing an average by the total row count.
- Assuming SUM returns 0 for empty or all-NULL input.
Likely follow-up questions
How can you count rows where salary is NULL?
Use SELECT COUNT(*) FROM employees WHERE salary IS NULL. Within a summary query, COUNT(*) - COUNT(salary) also gives the number of rows with a NULL salary.
How does COUNT(DISTINCT salary) handle NULLs?
It counts distinct non-NULL salary values. For 100, 100, NULL and 200, the result is 2.
What is the difference between AVG(COALESCE(salary, 0)) and COALESCE(AVG(salary), 0)?
AVG(COALESCE(salary, 0)) treats each missing salary as zero, including that row in the average's denominator. COALESCE(AVG(salary), 0) averages only known salaries and substitutes zero only when the aggregate result is NULL.
Interview tip: Use the 100, NULL, 200 example to explain COUNT(*) versus COUNT(salary), then show why AVG returns 150.