A count for every kind
Counting all the sales gives one number. The manager wants more: how many items does each category hold?
Asking once per category would be slow. One grouped question answers all of them.
Key words
| Word | What it means |
|---|---|
| Database | an organised store of tables that you can ask questions |
| SQL | the language used to ask a database for data |
| Query | one question written in SQL |
| GROUP BY | puts rows with the same value in a column into one group |
| Group | all the rows that share one value, such as every drink |
| COUNT | gives how many rows there are in each group |
| Summary row | one row in the answer that stands for a whole group |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can use GROUP BY with COUNT to count the rows in each group, and put the clauses in the right order.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
How GROUP BY works
Imagine sorting the products into piles, one pile for each category. GROUP BY category does that. Each pile becomes one row in the answer.
COUNT(*) then counts the rows inside each pile.
Try it: count each category
SELECT category, COUNT(*) AS items
FROM products
GROUP BY category;
-- How many sales did each payment type have?
SELECT paid_by, COUNT(*) AS sales
FROM sales
GROUP BY paid_by;
Worked example: piles of products
| category | Rows in the pile | COUNT(*) |
|---|---|---|
| drink | Cola, Orange juice, Water | 3 |
| fruit | Apple, Banana | 2 |
| lunch | Cheese roll, Chicken wrap, Cheese toastie | 3 |
| snack | Crisps, Cookie | 2 |
The cells students fill in are left blank in this preview.
The order of the clauses
A query with every clause has a fixed order. WHERE comes before GROUP BY, because rows are filtered first and then grouped. ORDER BY comes last.
Order: SELECT, FROM, WHERE, GROUP BY, ORDER BY.
Check the order
Have a go
Check the order
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.