meet it

A join builds one wide table for the rest of the query.

The cafe keeps customers, dishes and orders in separate tables. Each order holds a customer_id, a dish_id and a quantity: how many of that dish were ordered.

FROM and JOIN work first and build a : every order paired with its customer. From then on, WHERE, GROUP BY and ORDER BY treat it like any other table.

So grouping by c.name gives one row per customer, and SUM(o.quantity) adds up the items each one ordered.

SELECT c.name, SUM(o.quantity) AS items
FROM orders o
JOIN customers c ON o.customer_id = c.id
GROUP BY c.name
ORDER BY items DESC, c.name;

Sam has not ordered anything yet, so Sam vanishes from this join. A later lesson brings Sam back.

guess first

Brownies only. What comes back?

Guess before you run it. Wrong guesses are how you learn, and nothing is marked.

SELECT c.name, SUM(o.quantity) AS items
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.dish_id = 5
GROUP BY c.name
ORDER BY c.name;

your turn

Your turn. Find the best-selling dishes.

Type a number in the blank to list only the dishes that sold at least that many. Try 1, then try 4.

Your code

result

Your answer appears here as a table.

fix it

Ava ordered 4 items, but the report says 2. Fix it!

Broken code to fix

result

Your answer appears here as a table.

How many orders does Ava have, and what is in their quantity column?
COUNT(*) counts Ava's 2 orders. They hold 1 and 3 items, which need adding up.
Change COUNT(*) to SUM(o.quantity), then press run.

nailed it

you summed up a joined table. :D

FROM and JOIN build a joined table first.
WHERE, GROUP BY and ORDER BY then work on it.
COUNT(*) counts rows. SUM adds up the values in them.

new words in your collection

joined table

This lesson isn’t saved. A free account remembers every lesson you finish.