Aggregate functions

The five

SELECT COUNT(*)     AS orders,
       SUM(total)   AS revenue,
       AVG(total)   AS avg_order,
       MIN(total)   AS smallest,
       MAX(total)   AS largest
FROM orders;

With no GROUP BY, an aggregate collapses the whole table to a single row.

COUNT(*) versus COUNT(col)

• COUNT(*) counts rows.
• COUNT(col) counts non-null values of that column.
• COUNT(DISTINCT col) counts unique non-null values.

On a nullable column these give different answers, and which is correct depends on the question. COUNT(*) versus COUNT(email) is precisely how you find out how many customers have no email.

Nulls and aggregates

SUM, AVG, MIN and MAX all skip nulls. That is usually what you want, except for AVG, where it changes the divisor — wrap with COALESCE if nulls should count as zero.

Aggregates in WHERE

You cannot filter on an aggregate in WHERE:

-- fails
SELECT region, SUM(total) FROM orders
WHERE SUM(total) > 1000 GROUP BY region;

WHERE runs before grouping, so the aggregate does not exist yet. That is what HAVING is for.