Reporting Aggregated Data Using Group Functions
Reporting Aggregated Data Using Group Functions
This domain tests summarizing rows with group functions, forming groups, and filtering groups. The exam checks whether you know when WHERE applies and when HAVING applies, how nulls affect aggregates, and how subtotal extensions change result sets.
Core rules
- COUNT, SUM, AVG, MIN, and MAX operate on sets of rows. Except for COUNT(), group functions ignore nulls. COUNT(column) counts non-null values; COUNT() counts rows.
- Every nonaggregated select-list expression must appear in GROUP BY. GROUP BY forms one result row per distinct combination, treating nulls as one group.
- WHERE filters rows before grouping. HAVING filters groups after grouping and may contain group functions. A group function cannot appear in WHERE.
- HAVING without GROUP BY treats the whole table as one group.
- ROLLUP produces hierarchical subtotals; CUBE produces all combinations; GROUPING SETS selects specific groupings. GROUPING returns 1 for a subtotal row and 0 for a stored group value, which distinguishes subtotal nulls from data nulls.
Worked example
SELECT department_id, job_id, AVG(salary) AS avg_sal FROM employees GROUP BY ROLLUP(department_id, job_id) HAVING AVG(salary) > 5000 ORDER BY department_id, job_id;
The query groups by department and job, then adds department subtotals and a grand total. HAVING removes any group or subtotal whose average salary is not above 5000. ORDER BY arranges the surviving rows.
Traps
Using WHERE AVG(salary) > 5000 fails because WHERE runs before grouping. Selecting a column that is neither grouped nor aggregated causes an error. COUNT(commission_pct) can be much smaller than COUNT(*). ROLLUP order matters: ROLLUP(a,b) is not the same as ROLLUP(b,a). A null in a grouped column may be either stored data or a subtotal, so use GROUPING when the distinction matters.