GROUP BY

SQL · Unit 6: Grouping

Full plan
SQL
11 exercises

Last unit you got what the Devices stock is worth, 4937, with a WHERE. Then the owner wanted the number for Audio, for Displays, and for Accessories. It was the same query four times, with one word changed.

How much is the stock in each section worth? GROUP BY category deals the rows into piles, one per category, and the SUM runs once inside each pile. Each pile gives one row.

SELECT category,
  SUM(price * stock) AS value
FROM products
GROUP BY category;

Returns

category	value
Accessories	1143
Audio	898.5
Devices	4937
Displays	945

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

1. Multiple choice
orders has 12 rows, and 7 different products show up in them. The Speaker, which is product 8, doesn't, because nobody has ordered it. How many rows does this query return?
2. Predict the output
The store has 8 products. Three are accessories, two are audio, two are devices, and one, the Monitor, is the only one in Displays. What does this query return?
3. Complete the code
How many units has each customer ordered, across all their orders? Fill in the blank with the column that builds the groups.
4. Put the lines in order
How many orders came in each day? Put the query in order. Two lines don't belong.
5. Multiple choice
Tomorrow a new customer, Tyler, signs up, and he doesn't enter his city either. In unit 4 you saw that NULL = NULL isn't true. What happens to the groups in GROUP BY city?
6. Predict the output
Chloë has no city, but her age is on file. Hannah, who lives in Austin like Luke, has no age. What does this query return?
7. Find the bug
The owner wants to know how many customers are in each city, and how many have no city on file. This query says there are 0 with no city, but Chloë is right there in the table. Which line has the problem?
8. Put the lines in order
What does a product cost on average in each section? Put in order the query that gives it rounded to two decimals, with the sections from Z to A. One line doesn't belong.
9. Multiple choice
The owner asked how many customers are in each city. Here this query returns a single row, with Denver and an 8. Which line is it missing?
10. Predict the output
The accessories cost 45.5, 19.9, and 5.25. The Audio products cost 59.9 and 74, and the devices 899 and 320. The Monitor, alone in Displays, costs 189. What does this query return?
11. Write the code
The store wants to know when each customer last bought something, so it can write to the ones who have gone longest without coming back. Write a query with two columns, in this order: the customer_id and the date of that customer's latest order. One row for each customer who has ordered something.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →