SQL · Lesson 3 of 7

Sorting and Aggregates

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

• Beginner
• 15 min read
• 3 objectives

Before this lessonLesson 2: SELECT and WHERE

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.

From rows to answers

Reading raw rows is useful, but the questions people actually ask are summaries: "How many orders did we get?", "What is the average order value?", "Which country spends the most?". SQL answers these with aggregate functions. First, though, you often want the rows in a sensible order, which is what ORDER BY does.

ORDER BY and LIMIT

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

Sorting and limiting

SELECT name, age FROM customers ORDER BY age DESC

name  | age
------+----
Grace | 45
Ada   | 36
Linus | 28

SELECT id, total FROM orders ORDER BY total DESC LIMIT 2

id | total
---+------
10 | 120.0
12 | 80.0

ASC (ascending) is the default. LIMIT keeps only the first rows after sorting, which is how you build "top 10" lists and pages of results.

Summarising with aggregate functions

An aggregate takes many rows and returns 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

orders | revenue | average | biggest
-------+---------+---------+--------
3      | 235.5   | 78.5    | 120.0

GROUP BY: one summary per group

Without GROUP BY an aggregate collapses everything into a single row. Add GROUP BY column and you get one result row per distinct value, which is how you answer "per country", "per month" or "per customer" questions.

SELECT status, COUNT(*) AS n, SUM(total) AS money FROM orders GROUP BY status

status   | n | money
---------+---+------
paid     | 2 | 155.5
refunded | 1 | 80.0

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 order SQL actually runs things

You write SELECT first, but the database processes clauses in a different order. Knowing it explains most error messages.

• FROM: pick the table.
• WHERE: throw away rows that do not match.
• GROUP BY: bundle the remaining rows into groups.
• HAVING: throw away whole groups that do not match.
• SELECT: compute the columns to show.
• ORDER BY then LIMIT: sort and trim the final result.

WHERE versus HAVING, with a real example

SELECT customer_id, SUM(total) AS spent FROM orders WHERE status = 'paid' GROUP BY customer_id HAVING SUM(total) > 100

customer_id | spent
------------+------
1           | 155.5

WHERE status = 'paid' removes the refunded order before grouping. HAVING SUM(total) > 100 then removes customers whose paid total is too small after grouping. You cannot put an aggregate inside WHERE.

Key takeaways

• ORDER BY sorts (DESC for descending); LIMIT keeps the top rows.
• Aggregates (COUNT, SUM, AVG, MIN, MAX) turn many rows into one value.
• GROUP BY gives one result row per group.
• WHERE filters rows before grouping; HAVING filters groups after.

-- Write your solution here