Group Functions
What the domain covers
Group functions aggregate many rows into one result. This domain tests COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING, and null handling.
Core rules
- COUNT(*) counts rows, including rows with nulls. COUNT(expr) counts non-null values. COUNT(DISTINCT expr) counts distinct non-null values.
- SUM and AVG ignore nulls. AVG(expr) is the sum of non-null values divided by the count of non-null values, not by total rows.
- MIN and MAX work on numbers, dates, and character strings.
- GROUP BY forms groups; each group returns one row. Every nonaggregated column in SELECT must appear in GROUP BY.
- WHERE filters rows before grouping. HAVING filters groups after grouping and can contain group functions.
- ORDER BY can sort by a group function or its alias.
Worked example
SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id HAVING COUNT(*) > 5 ORDER BY avg_sal DESC;
Reasoning: rows are grouped by department. AVG ignores null salaries. HAVING keeps only departments with more than five rows, including rows whose salary is null because COUNT() counts rows. ORDER BY uses the alias. If HAVING were replaced with WHERE COUNT() > 5, it would fail because WHERE cannot evaluate group functions.
Traps
Do not put a group function in WHERE. A select list item such as last_name with AVG(salary) and no GROUP BY is invalid. COUNT(column) can be less than COUNT(). AVG(column) ignores nulls, so it can differ from SUM(column)/COUNT().