Sorting and Aggregates

ORDER BY, LIMIT, COUNT, SUM, AVG and GROUP BY with HAVING.

So far we have listed rows. Real questions are usually about order (who is biggest?) and summaries (how much in total?). This lesson covers both.

SELECT name, age FROM customers
ORDER BY age DESC, name ASC
LIMIT 2;

name  | age
Grace | 45
Ada   | 36

ORDER BY sorts ascending by default; add DESC for descending and list several columns to break ties. LIMIT n keeps the first n rows, and OFFSET skips rows for pagination. Without ORDER BY, row order is not guaranteed.

Aggregate functions

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

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

orders | revenue | average | biggest
2      | 155.5   | 77.75   | 120.0

COUNT(*) counts rows; COUNT(column) counts rows where the column is not NULL; COUNT(DISTINCT column) counts unique values.

GROUP BY

GROUP BY splits rows into buckets and runs the aggregates once per bucket.

SELECT customer_id, COUNT(*) AS orders, SUM(total) AS spent
FROM orders
GROUP BY customer_id
ORDER BY spent DESC;

customer_id | orders | spent
1           | 2      | 155.5
3           | 1      | 80.0

The rule: every column in SELECT must either appear in GROUP BY or be inside an aggregate function.

HAVING versus WHERE

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

SELECT customer_id, SUM(total) AS spent
FROM orders
WHERE status = 'paid'          -- rows first
GROUP BY customer_id
HAVING SUM(total) > 100;       -- then groups

SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
HAVING COUNT(*) >= 2
ORDER BY customers DESC;