1Z0-149: Reporting Aggregated Data Using Group Functions

What this domain tests

Group functions summarize sets of rows. The exam tests GROUP BY, HAVING, aggregate NULL handling, and grouping extensions.

Core rules

• AVG, SUM, MIN, MAX, COUNT are group functions. They return one row per group.
• COUNT(*) counts rows; COUNT(column) counts non-NULL values; COUNT(DISTINCT column) counts distinct non-NULL values.
• All group functions except COUNT(*) ignore NULLs. AVG(column) divides by the number of non-NULL values, not total rows.
• Every nonaggregate expression in the select list must appear in GROUP BY.
• WHERE filters rows before grouping; HAVING filters groups after grouping. HAVING may contain group functions.
• ORDER BY can use a group function or an alias.
• ROLLUP, CUBE, and GROUPING SETS produce subtotals and totals; GROUPING distinguishes stored NULLs from generated summary NULLs.

Worked example

SELECT department_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary FROM employees WHERE salary IS NOT NULL GROUP BY department_id HAVING COUNT(*) > 3 ORDER BY avg_salary DESC;

WHERE removes rows with no salary before grouping. GROUP BY forms one group per department. HAVING keeps only groups with more than three rows. The average uses only non-NULL salaries. The final sort uses an aggregate alias.

Traps

• Putting an aggregate in WHERE; use HAVING.
• Selecting last_name with department_id without grouping by both.
• Assuming AVG ignores NULLs in the denominator; it does, which changes the result.
• Nesting group functions, such as MAX(AVG(salary)), without a valid grouping context.
• Confusing a NULL group (all rows with NULL department) with a ROLLUP subtotal.