Counting and grouping

From rows to answers

Managers don't want 10,000 rows — they want "how many orders per city?" or "average price per category". Aggregate functions squash many rows into one value, and GROUP BY does it per group.

Aggregates and groups

SELECT count(*) AS dishes, min(price), max(price), round(avg(price)) AS avg_price FROM dishes;

SELECT category, count(*) AS n, round(avg(price)) AS avg_price
FROM dishes
GROUP BY category
ORDER BY avg_price DESC;

SELECT category, count(*) AS n
FROM dishes
GROUP BY category
HAVING count(*) >= 3;

Every selected column must be in GROUP BY or inside an aggregate.
count(*) counts rows; count(column) skips NULLs.

From rows to answers

Aggregates and groups

The rest of this lesson