Aggregate functions summarise collections of values across multiple rows to produce a single condensed scalar output.
They form the primary analytical tool for computing descriptive statistics directly inside relational database engines.
The COUNT function has two distinct modes: COUNT(*) counts all rows, while COUNT(column) counts non-null values.
If a column contains 3 numbers and 2 NULL entries, COUNT(column) returns 3, whereas COUNT(*) returns 5.
The SUM function adds all non-null numerical values in a column, returning NULL if every input row is NULL.
The AVG function computes the arithmetic mean by summing non-null values and dividing by the count of non-null rows.
If all rows contain NULL, AVG returns NULL rather than triggering an arithmetic division-by-zero error.
The MIN and MAX functions identify the lowest and highest values, functioning on dates and text as well as numbers.
Adding the DISTINCT keyword inside an aggregate function restricts calculation strictly to unique values.
For example, COUNT DISTINCT customer_id computes the number of unique purchasing clients across repeated transaction logs.
Aggregate functions cannot be nested directly inside each other: writing AVG of SUM is invalid SQL syntax.
To aggregate an aggregate, analysts must use a subquery or common table expression to stage calculations sequentially.
Database engines optimise aggregate queries using parallel scans and pre-computed index statistics where available.
Approximate aggregate functions, such as HyperLogLog algorithms, estimate distinct counts rapidly over massive datasets.
A frequent error is assuming that AVG divides by total row count when NULL values are present, rather than non-null rows.