Counting and grouping

COUNT, SUM, AVG and GROUP BY to summarize data.

Aggregate functions collapse many rows into one value: COUNT, SUM, AVG, MIN, MAX.

SELECT COUNT(*) FROM users;                 -- how many rows
SELECT AVG(age) FROM users;                 -- average age
SELECT MAX(age) FROM users WHERE country = 'US';

GROUP BY

GROUP BY makes one result row per group. With our users table:

SELECT country, COUNT(*) AS total
FROM users
GROUP BY country;

WHERE vs HAVING

WHERE filters rows before grouping. HAVING filters the groups after aggregation.

SELECT country, COUNT(*) AS total
FROM users
GROUP BY country
HAVING COUNT(*) > 1;   -- only countries with more than one user

CarefulEvery column in SELECT must be in GROUP BY or inside an aggregate function. Some databases reject the query otherwise, others return arbitrary values.