Aggregate functions and grouping

The essentials

Aggregate (multiple-row) functions take a set of rows and return one value. With GROUP BY, they return one value per group.

Key points

• MAX(col), MIN(col): largest and smallest values.
• SUM(col), AVG(col): total and average of numeric values.
• COUNT(*): counts all rows, including rows with NULLs.
• COUNT(col): counts non-NULL values in that column. COUNT(DISTINCT col) counts different non-NULL values.
• SUM, AVG, MAX, MIN and COUNT(col) all ignore NULL values. For values 10, 20, NULL, AVG gives 15, not 10.
• GROUP BY col: forms groups of rows with the same value in col. Example: SELECT Dept, AVG(Salary) FROM Emp GROUP BY Dept;
• HAVING: puts a condition on groups, usually using an aggregate. Example: … GROUP BY Dept HAVING COUNT(*) > 3;
• WHERE filters individual rows before grouping; aggregate functions cannot be used in WHERE.
• ORDER BY sorts the final result, ASC by default or DESC.
• Order of clauses: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY.

Exam tips

• For an output question, first apply WHERE, then form groups, then apply HAVING, then sort.
• The number of rows returned by a GROUP BY query equals the number of groups that survive HAVING.

Common mistakes

• Writing WHERE SUM(Marks) > 100. Conditions on aggregates go in HAVING.
• Assuming COUNT(col) equals COUNT(*) when the column contains NULLs.