Aggregating with Group Functions
How aggregates behave
Group functions (COUNT, SUM, AVG, MIN, MAX, also STDDEV, VARIANCE) collapse many rows to one value. Every one of them ignores NULLs - except COUNT(*), which counts rows regardless of content. Consequently AVG(commission_pct) divides by the number of non-null commissions, not by the row count, and SUM of an all-NULL set is NULL.
Grouping and filtering
GROUP BY forms groups of rows sharing the listed column values; the select list may then contain only grouped columns and group functions. WHERE filters individual rows before grouping; HAVING filters groups after aggregation. Putting a group function in WHERE is a syntax error - that is HAVING's job. You may not nest group functions directly (MAX(AVG(x)) needs an inline view).
Multiple columns in GROUP BY make finer groups. Rows with NULL in a grouped column form their own group.
Traps
Omitting a non-aggregated select column from GROUP BY raises ORA-00979. Group functions may appear in ORDER BY. COUNT(column) and COUNT(*) differ whenever NULLs exist. HAVING without GROUP BY is legal and treats the whole table as one group.
Worked example
SELECT department_id, AVG(salary) AS avg_sal FROM employees WHERE salary > 3000 GROUP BY department_id HAVING AVG(salary) > 6000 ORDER BY avg_sal DESC;
The WHERE removes low earners before grouping; HAVING then keeps only departments whose average exceeds the threshold.