Single-Row and Group Functions, and Grouping Data
Single-Row and Group Functions
This domain tests scalar functions that act on one row and aggregate functions that summarize groups, plus the rules for GROUP BY and HAVING.
Core rules
- Single-row functions return one value per row: NVL, NVL2, COALESCE, NULLIF, CASE, DECODE, TO_CHAR, TO_DATE, ROUND, TRUNC, SUBSTR, INSTR, UPPER, LOWER, and SYSDATE/CURRENT_DATE.
- Group functions return one value per group: AVG, COUNT, SUM, MIN, MAX. They ignore NULLs except COUNT(*), which counts rows.
- COUNT(column) counts non-NULL values; COUNT(DISTINCT column) counts distinct non-NULL values.
- WHERE filters rows before grouping; HAVING filters groups after grouping. A group function cannot appear in WHERE.
- Every nonaggregated select-list column must appear in GROUP BY.
- Group functions cannot be nested directly, such as MAX(COUNT(*)); use an inline view.
Worked example
SELECT department_id, COUNT() AS headcount, AVG(salary) AS avg_salary FROM employees WHERE salary > 5000 GROUP BY department_id HAVING COUNT() >= 2 ORDER BY headcount DESC; WHERE removes low salaries before aggregation; GROUP BY forms departments; HAVING keeps departments with at least two remaining rows; AVG ignores any NULL salaries.
Traps
- Putting a group function in WHERE instead of HAVING.
- Selecting a column not in GROUP BY.
- Assuming AVG includes NULLs; it does not.
- Using COUNT(column) when you need all rows; COUNT(*) is different.
- Nesting group functions without a subquery.