Aggregates, GROUP BY & HAVING: Summarizing Rows

Aggregates turn many rows into one value. COUNT(*) counts rows, SUM(amount) adds, AVG(amount) averages, and MAX / MIN find the extremes. With no GROUP BY, the whole table is a single group:

SELECT COUNT(*)    AS orders,
       SUM(amount)  AS revenue,
       MAX(amount)  AS biggest
FROM orders
WHERE status = 'paid';

Filter before aggregating with WHERE: the query above sums only paid orders. An aggregate cannot appear in WHERE, because WHERE runs before any grouping has happened.

GROUP BY splits rows into groups that share the same value and computes the aggregates once per group:

SELECT customer_id,
       COUNT(*)    AS orders,
       SUM(amount) AS spent
FROM orders
GROUP BY customer_id;

The rule: every column in SELECT must either be inside an aggregate or be listed in GROUP BY. SELECT customer_id, amount ... GROUP BY customer_id is ambiguous - which of the customer's amounts should appear? - so strict databases reject it. (SQLite quietly picks one row's value, which is a bug waiting to happen.)

HAVING filters groups, WHERE filters rows. To keep only customers whose paid orders total more than 1,000:

SELECT customer_id, SUM(amount) AS spent
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) > 1000
ORDER BY spent DESC;

Logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. That order explains every rule above: WHERE cannot see group totals (they do not exist yet) and HAVING can (they do).

Counting pitfalls. COUNT(*) counts rows, COUNT(col) skips NULLs, and COUNT(DISTINCT col) counts unique values: 'how many different customers ordered?' is COUNT(DISTINCT customer_id). Averages ignore NULLs too, so a missing value is never treated as zero.

Top group: 'the vendor with the largest total' is GROUP BY plus SUM, then ORDER BY total DESC LIMIT 1. For the single largest value overall just use MAX; to fetch the whole row that holds it, use a subquery (see the Subqueries concept).