Counting and summarizing

Collapse many rows into one number.

One number from many rows

Aggregate functions reduce a whole column to a single value:

COUNT(*) — number of rows
SUM(price), AVG(price) — total / average
MIN(price), MAX(price) — smallest / largest

SELECT COUNT(*) AS books, AVG(price) AS avg_price
FROM books;

Aggregates ignore NULL (except COUNT(*)). They also respect WHERE, so you can summarize a subset.

An aggregate reads a whole column and returns one value, so the result has one row no matter how many went in. A WHERE clause changes which rows arrive, never how many come out.

Aggregates skip NULLs, so COUNT(published) is a count of known years, not of books. COUNT(*) counts rows and is the only one that cannot be fooled by a missing value.