GROUP BY and Aggregate Functions

GROUP BY

The GROUP BY construct partitions rows into groups. For each group, aggregate functions reduce the group to a single scalar value:

SELECT course, MIN(mark), MAX(mark), AVG(mark)
FROM marks
GROUP BY course;

This returns one row per course with the minimum, maximum, and average mark for that course.

Aggregate functions

Function	Description
MIN(expr)	Minimum value in the group
MAX(expr)	Maximum value in the group
AVG(expr)	Arithmetic mean of non-NULL values
COUNT(expr)	Number of non-NULL values
COUNT(*)	Number of rows (including NULLs)
SUM(expr)	Sum of non-NULL values

SELECT clause restrictions

The SELECT clause can only contain:

1. Columns that appear in the GROUP BY clause.
2. Expressions involving aggregate functions.

Columns not in GROUP BY and not wrapped in an aggregate are illegal, because within a group there may be multiple values for that column — which one would be selected?

HAVING

The HAVING clause filters groups, just as WHERE filters rows, but applied after grouping:

SELECT course, AVG(mark) AS avg_mark
FROM marks
GROUP BY course
HAVING AVG(mark) >= 70;

Clause	Filters	Applied
WHERE	Individual rows	Before grouping
HAVING	Groups	After grouping

Order of execution

The logical order of execution for a SELECT query with grouping:

1. FROM — identify the source table(s)
2. WHERE — filter rows
3. GROUP BY — partition into groups
4. HAVING — filter groups
5. SELECT — project columns and compute aggregates
6. ORDER BY — sort the result

Note that column aliases defined in the SELECT clause are not visible in WHERE or GROUP BY.

Properties of aggregates

The reduction operator must be commutative and associative so that the grouping order does not affect the result. All standard aggregate functions satisfy this:

$f(a, b) = f(b, a) \quad \text{(commutative)}$ $f(a, f(b, c)) = f(f(a, b), c) \quad \text{(associative)}$

This guarantees that whether the database processes groups in parallel or serially, the result is deterministic.

COUNT and NULLs

COUNT(*) counts all rows, including those with NULL values. COUNT(column) counts only non-NULL occurrences of that column:

SELECT COUNT(*), COUNT(mark) FROM marks;

If some mark values are NULL, these two counts differ.

Summary

• GROUP BY partitions rows; aggregates reduce each group to a scalar.
• SELECT may only reference grouping columns or aggregates.
• HAVING filters groups after grouping; WHERE filters rows before grouping.
• Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
• COUNT(*) includes NULLs; COUNT(column) excludes them.
• Aggregate functions are commutative and associative, enabling parallel evaluation.