The Monday report
Every Monday the coffee shop owner asks the same thing. "How many orders did each city send us, and how much were they worth?"
You could copy the orders into a spreadsheet and add them up by hand. Or the database could count and total them for you in one query.
By the end of this lesson
Note
I can count and total rows in groups with SQL.
Key words
| Word | What it means | Example |
|---|---|---|
| Sort | to put rows in order | |
| Aggregate | one answer worked out from many rows | |
| COUNT | how many rows there are | COUNT(*) |
| SUM | the values of a column added up | SUM(total) |
| Group | the rows that share one value |
The cells students fill in are left blank in this preview.
ORDER BY puts rows in order
A database gives rows back in no promised order. If order matters, ask for it.
ORDER BY total sorts from smallest to largest. Add DESC to sort from largest to smallest. LIMIT 3 keeps only the first three rows of the answer. Together they answer "what were our three biggest orders?"
Try it: the biggest orders
-- The biggest orders first: DESC means largest to smallest.
SELECT id, customer, total FROM orders
ORDER BY total DESC;
-- Only the top three.
SELECT id, customer, total FROM orders
ORDER BY total DESC
LIMIT 3;
A common mistake
Warning
Many people think ORDER BY total puts the biggest first. It puts the smallest first. Ascending is the default. Add DESC whenever you want the largest values at the top.
COUNT and SUM give one answer
COUNT(*) counts rows. SUM(total) adds up the total column. Each is an aggregate: many rows go in, one answer comes out.
AS gives the answer a clear name, such as takings. A WHERE still works. It removes rows first, then the count and the sum use what is left.
Try it: count and add up
-- One answer for the whole table.
SELECT COUNT(*) AS orders, SUM(total) AS takings
FROM orders;
-- The same, for the open orders only.
SELECT COUNT(*) AS orders, SUM(total) AS takings
FROM orders
WHERE status = 'open';
GROUP BY gives one row per group
GROUP BY city puts the rows into piles, one pile for each city. Then COUNT and SUM work on each pile separately.
The answer has one row per city. Put the grouped column in SELECT too, or you will not know which row is which city.
Worked example: GROUP BY city
| city | Orders in the pile | COUNT(*) | SUM(total) |
|---|---|---|---|
| Leeds | 101, 103, 106 | 3 | 42 + 65 + 12 = 119 |
| Bristol | 102, 105 | 2 | 18 + 90 = 108 |
| Cardiff | 104, 107 | 2 | 27 + 55 = 82 |
The cells students fill in are left blank in this preview.
Try it: a row for each city
-- One row for each city: how many orders, and their total.
SELECT city, COUNT(*) AS orders, SUM(total) AS takings
FROM orders
GROUP BY city
ORDER BY takings DESC;
-- Change city to status in both places, then press Run.