Aggregates turn many rows into one answer.
Direct answer
Aggregate functions summarise rows: COUNT, SUM, AVG, MIN and MAX. Use GROUP BY to calculate them per group and HAVING to filter the groups. Use WHERE to filter rows first.
Examples
SELECT COUNT(*) FROM orders;
SELECT SUM(total) AS revenue, AVG(total) AS average_order
FROM orders;
SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS spent
FROM orders
GROUP BY customer_id;
SELECT customer_id, SUM(total) AS spent
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 500
ORDER BY spent DESC;
WHERE vs HAVING
WHERE | HAVING | |
|---|---|---|
| Filters | rows | groups |
| Runs | before grouping | after grouping |
Notes
COUNT(*)counts rows,COUNT(column)ignores NULLs.- Every non-aggregated column in
SELECTmust be inGROUP BY.
Where Teyro fits
Teyro's SQL and Databases for Beginners course turns this into short lessons you can play in a few minutes a day. It has 50 lessons, and you can start free.
The bottom line
Group, aggregate, filter the groups.
