How much did we sell today?
At the end of a shift at a café, the manager asks two questions. How many sales did we make? How much money came in for drinks, and how much for food?
The till has a record for every sale. Nobody wants to add them up by hand. A database can count and total them for you.
By the end of this lesson
Note
I can summarise data with COUNT, SUM and GROUP BY.
Key words
| Word | What it means | Example |
|---|---|---|
| Aggregate | a function that turns many values into one | |
| COUNT | how many records there are | COUNT(*) |
| SUM | adds up the values in a field | SUM(stock) |
| Group | records that share the same value in a field | |
| GROUP BY | makes one group for each different value |
The cells students fill in are left blank in this preview.
COUNT and SUM turn many records into one
COUNT(*) counts the records. SUM(stock) adds up the stock field. Each gives back one number, not a list.
A WHERE still works. It picks the records first, then the aggregate counts or adds only those.
Sign in, then count and total some of the shop.
Let the terminal trust the server
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make a short name for the shop
alias sql='mariadb -h db -u student -pstudent shop -t -e'Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Count the customers
sql "SELECT COUNT(*) AS customers FROM customers"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Count and total the garden stock
sql "SELECT COUNT(*) AS items, SUM(stock) AS units FROM products WHERE category_id = 3"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
GROUP BY gives one answer per group
GROUP BY country puts the customers into one group for each country. Then COUNT(*) counts each group on its own.
You get one record for each group, not one for each customer. The worked example shows a café till grouped by kind.
Worked example: grouping a till
| Sales on the till | Group (kind) | COUNT(*) | SUM(pence) |
|---|---|---|---|
| Cola 120, Water 90, Juice 150 | drink | 3 | 360 |
| Crisps 85, Cookie 110 | snack | 2 | 195 |
| Sandwich 325 | food | 1 | 325 |
The cells students fill in are left blank in this preview.
Customers in each country
sql "SELECT country, COUNT(*) AS customers FROM customers GROUP BY country ORDER BY customers DESC, country"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Orders in each status
sql "SELECT status, COUNT(*) AS orders FROM orders GROUP BY status ORDER BY orders DESC"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Stock in each category, top three
sql "SELECT categories.name, SUM(products.stock) AS units FROM products JOIN categories ON categories.id = products.category_id GROUP BY categories.name ORDER BY units DESC LIMIT 3"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A common mistake
Warning
Many people think you can show any field beside a count. A group of 14 customers has many cities, so the database cannot pick one. Every field you show must be in GROUP BY, or inside an aggregate.
Show a field that is not grouped
sql "SELECT country, city, COUNT(*) AS customers FROM customers GROUP BY country"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Going further
WHERE tests records before grouping. HAVING tests the groups after grouping.
Add HAVING COUNT(*) >= 8 to the countries query, before ORDER BY. Which countries are left?