Group Functions and Aggregated Data
Domain 5 tests aggregation across sets of rows: SUM, AVG, MIN, MAX, COUNT, GROUP BY, and HAVING. You must distinguish row-level filtering from group-level filtering and understand NULL treatment.
Core rules
- Group functions operate on many rows and return one result per group, or one result for the whole table when no GROUP BY exists.
- SUM, AVG, MIN, and MAX ignore NULLs. COUNT(*) counts rows; COUNT(column) counts non-NULL values in that column; COUNT(DISTINCT column) counts distinct non-NULL values.
- Every nonaggregated column in the SELECT list must appear in GROUP BY. Single-row functions can wrap grouped columns or aggregate results.
- WHERE filters rows before grouping. HAVING filters groups after grouping and can use group functions.
- Group functions can be nested, such as MAX(AVG(salary)), when the inner aggregate is valid for the grouping level.
- ORDER BY can sort by an aggregate expression or alias after grouping.
Worked example
SELECT department_id, AVG(salary) AS avg_sal, COUNT(*) AS headcount FROM employees GROUP BY department_id HAVING AVG(salary) > 6000 ORDER BY department_id;
FROM reads employees. GROUP BY forms one group per department_id. AVG computes the average salary in each group, ignoring NULL salaries. COUNT(*) counts all rows in each group. HAVING removes groups whose average is not above 6000. ORDER BY sorts the surviving groups.
Traps
Putting AVG(salary) in WHERE fails; use HAVING. Selecting last_name without grouping it fails because it is not functionally dependent on the group. COUNT(column) is lower than COUNT(*) when NULLs exist. A query with HAVING but no GROUP BY treats the whole table as one group. AVG ignores NULLs, so it is not the same as SUM divided by total row count.
Key points
- WHERE filters rows; HAVING filters groups.
- COUNT(*) counts rows; COUNT(col) ignores NULLs.
- Nonaggregated SELECT columns must be grouped.
- AVG ignores NULLs.
- Group functions can be nested in valid contexts.