SQL Aggregate Functions
Aggregate functions collapse many rows into one summary value. They're the backbone of reports, dashboards, and analytics.
The big five
| Function | What it returns |
|---|---|
COUNT(*) | Number of rows, including NULLs. |
COUNT(col) | Number of non-NULL values in col. |
SUM(col) | Total of numeric values. |
AVG(col) | Arithmetic mean. |
MIN(col) / MAX(col) | Smallest / largest value. |
Per-group aggregation
Pair with GROUP BY to bucket rows before summarising:
SQL
SELECT country,
COUNT(*) AS customers,
AVG(lifetime_value) AS avg_value
FROM customers
GROUP BY country
ORDER BY customers DESC;
Aggregates and NULL
- All aggregates except
COUNT(*)skip NULLs. SUMof zero rows returnsNULL, not 0. Wrap inCOALESCE(SUM(x), 0)if you need a number.
Tip: Anywhere you'd reach for a loop in code to compute a total or average, you almost certainly want an aggregate query instead. Let the DB do the work.
Example
Example
SELECT COUNT(*) AS rows,
AVG(price) AS avg_price,
MAX(price) AS max_price
FROM products;
Try it Yourself »
Exercise
Aggregate that always counts NULL rows.
SELECT
(*) FROM customers;
Five letters; the row-counting function.
Discussion
Loading…