SQL Café · Lesson 4 of 8

Please put them into piles first.

So far: SELECTFROMWHEREORDER BYLIMITCOUNT SUM AVG MIN MAX Lesson 1 · Lesson 2 · Lesson 3

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.

You say to the waiter “Please could you tell me the average price for each type: one for the drinks and one for the food? Thank you!”
↓ same thing in SQL
SELECT type, AVG(price) FROM menu GROUP BY type;
The one thing to remember

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.

8 rows
GROUP BY type makes 2 piles
drink £3.63food £4.00
AVG(price) squashes each pile: 2 rows
a drink rowa food row
Compare with Lesson 3: without GROUP BY, all the rows left after WHERE make one big pile, so you get one row. With GROUP BY type, there are two piles, so you get two rows. One pile in, one row out, for every pile.
About the numbers: the drinks add up to £14.50, and £14.50 ÷ 4 = £3.625, shown here rounded to £3.63. The food adds up to £16.00, and £16.00 ÷ 4 = £4.00.
The pile rule

Pile label, or inside brackets

Every column after SELECT must be either the pile label (a column named in GROUP BY) or inside a receipt word.
✓
SELECT type, COUNT(*) FROM menu GROUP BY type

type is the pile label, and COUNT(*) is inside brackets. Every pile can show both.

✗
SELECT type, item, COUNT(*) FROM menu GROUP BY type

The 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 type

Putting item inside a receipt word fixes it: “the first item from A to Z in each pile”.

A second new word

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 Which rows? Before the piles are made WHERE calories < 200

Throws away single rows. Receipt words are not allowed here.

HAVING Which piles? After each pile is squashed 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.

The full order, in one sentence

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.

SomeFriendlyWaitersGladlyHandOutLemonade
SELECT→FROM→WHERE→GROUP BY→HAVING→ORDER BY→LIMIT

You can still skip the ones you don't need. HAVING is used together with GROUP BY.

Read the code one word at a time

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 it out loud
Today's words and symbols

Say each one out loud

Swap each word for what you say, and the code becomes a normal sentence again.

WriteSay it asExampleWhy it's there
Making piles
GROUP BY“with one pile per…”GROUP BY typeTwo words that always go together. Rows with the same type go into the same pile.
,“and”GROUP BY type, priceA 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(*) > 3A test for each pile, after it is squashed. Receipt words are allowed here.
Sorting piles
ORDER BY“sorted by…”ORDER BY AVG(price) DESCYou can sort the answer by a receipt word too. Without ORDER BY, the order of the piles is not guaranteed.
Your turn

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.

Mission

Ctrl + Enter also runs
Or try one of these
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 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.

Lock it in

Five quick questions

Answering from memory is the fastest way to make something stick. Wrong answers explain themselves, so please don't worry.

Take this with you

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.