Please put them into piles first.
In Lesson 3 the receipt words squashed the whole menu into one answer. But what if you want one answer for the drinks and another for the food? Today you learn to put the rows into piles first, then get one answer per pile.
Make piles, then squash each pile
GROUP BY works like a waiter putting the menu cards into piles: all the drinks in one pile, all the food in another. Then the receipt words squash each pile into one row. So you get one row per pile.
Pile label, or inside brackets
SELECT type, COUNT(*) FROM menu GROUP BY typetype is the pile label, and COUNT(*) is inside brackets. Every pile can show both.
SELECT type, item, COUNT(*) FROM menu GROUP BY typeThe drinks pile has four different items. Which one should its single row show? There is no right answer, so most databases give an error.
SELECT type, MIN(item) FROM menu GROUP BY typePutting item inside a receipt word fixes it: “the first item from A to Z in each pile”.
WHERE picks rows, HAVING picks piles
Sometimes you only want some of the piles, for example “only the types that cost more than £3.80 on average”. WHERE can't do this, because WHERE checks one row at a time, before any piles exist. That is what HAVING is for.
WHERE calories < 200
Throws away single rows. Receipt words are not allowed here.
HAVING AVG(price) > 3.8
Throws away whole piles. Here only food is kept, because its average is £4.00 and the drinks average is £3.625.
Some Friendly Waiters Gladly Hand Out Lemonade
Lesson 2's sentence grows by two words. GROUP BY and HAVING go between WHERE and ORDER BY.
You can still skip the ones you don't need. HAVING is used together with GROUP BY.
Every piece has a meaning
Please press Next (or click any piece) and watch the sentence grow underneath. Today's new piece is grape coloured.
Say each one out loud
Swap each word for what you say, and the code becomes a normal sentence again.
| Write | Say it as | Example | Why it's there |
|---|---|---|---|
| Making piles | |||
| GROUP BY | “with one pile per…” | GROUP BY type | Two words that always go together. Rows with the same type go into the same pile. |
| , | “and” | GROUP BY type, price | A pile for each different pair: drinks at £3.50, food at £3.50, and so on. |
| Choosing piles | |||
| HAVING | “but only piles where…” | HAVING COUNT(*) > 3 | A test for each pile, after it is squashed. Receipt words are allowed here. |
| Sorting piles | |||
| ORDER BY | “sorted by…” | ORDER BY AVG(price) DESC | You can sort the answer by a receipt word too. Without ORDER BY, the order of the piles is not guaranteed. |
Playground: watch the piles form
Please type a query and press Run. Watch the rows slide into piles, then see each pile squash into one row.
- 1
- 2
- 3
- 4
- 5
- 6
- 7
Order of work: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. The receipt words are worked out for each pile as soon as the piles are made.
Five quick questions
Answering from memory is the fastest way to make something stick. Wrong answers explain themselves, so please don't worry.
Your pocket card
Please take a screenshot of this card. Read it once tomorrow and once next week.
GROUP BYOne pile per what?HAVINGWhich piles?- Make piles, then squash each pile: one row per pile.
- The pile rule: after SELECT, use the pile label or a receipt word.
- WHERE picks rows (before the piles). HAVING picks piles (after the squash).
- Receipt words can go in HAVING, never in WHERE.
- Order to write: Some Friendly Waiters Gladly Hand Out Lemonade.
- Want the piles in a set order? Add ORDER BY.
SQL Café is a free course by MASSH AI. Want SQL or data training for your team? Please get in touch.