Grouping by several columns

SQL · Unit 6: Grouping

Full plan
SQL
10 exercises

How many products in each section can be sold today? The ones that are out of stock don't count, and that's a condition on each product. It goes in a WHERE, as always.

The WHERE is written before the GROUP BY and runs before it. First it removes rows, and the groups are built from what's left. By the time the COUNT arrives, the Cable and the Speaker are gone.

SELECT category,
  COUNT(*) AS available
FROM products
WHERE stock > 0
GROUP BY category;

Returns

category	available
Accessories	2
Audio	1
Devices	2
Displays	1

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. Predict the output
Two products are out of stock: the Cable, in Accessories, and the Speaker, in Audio. What does this query return?
2. Put the lines in order
Today is January 15, 2026. How many orders does each customer have up to today, without the order dated the 20th? Put the query in order. One line doesn't belong.
3. Predict the output
Orders of 3 units or more ship in a big box. There are three: one of 5 units from customer 2, one of 10 from customer 4, and one of 3 from customer 5. Across all their orders, customer 2 has 8 units and customer 5 has 4. What does this query return?
4. Complete the code
Someone fixes the report to leave out anyone with no age on file, and sorts it. Fill in the line that builds one group per decade.
5. Predict the output
The row with the empty city in the customers-by-city report bothers someone, and they "fix" it like this. Without the fix, Denver has 3 customers, Austin 2, Boston 1, and Tampa 1, and that leaves Chloë, with no city. What does this query return?
6. Multiple choice
orders has 12 rows, from 6 customers and 7 different products, and no customer has ordered the same product twice. Without the WHERE above, how many rows does a query with GROUP BY customer_id, product_id return?
7. Predict the output
Customer 1 has placed orders of 2, 1, and 1 units. Customer 6 has placed two, of 1 unit each. What does this query return?
8. Find the bug
The owner asked how many orders each customer has placed, with one row per customer. Something from an earlier version was left in this query, and customer 1 shows up three times, each time with a 1. Which line has the problem?
9. Put the lines in order
Has any customer placed two orders on the same day? Put in order the query that counts each customer's orders on each date, most recent first. One line doesn't belong.
10. Find the case that fails
This query looks for duplicate records in customers, the same person entered twice. Two records are the same person if the name and the city match, and the query has to say how many times each person is there. Which of these changes makes it count two different people as one?

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 →