MediumSQL challenge #169JOINGROUP BY

Orders with User & Items

Find order id, user id and total items.

The challenge runs on the SQLabHub practice database (SQLite) and uses the orders and order_items tables. You can solve it in the browser, with nothing to install.

Solve This SQL Challenge

The task

Find order id, user id and total items.

A correct answer returns 200 rows with 3 columns: id, user_id, SUM(oi.quantity).

Tables used in this challenge

`orders` 200 rows

id INTEGER (PK) · user_id INTEGER · total REAL · discount REAL · item_count INTEGER · status TEXT · order_date TEXT · shipped_date TEXT · shipping_city TEXT · shipping_country TEXT

First 3 rows of the orders table
id	user_id	total	discount	item_count	status	order_date	shipped_date	shipping_city	shipping_country
1	43	2638.11	0.25	3	cancelled	2024-04-24	NULL	Los Angeles	USA
2	8	2947	0.19	5	delivered	2024-03-15	2023-04-09	Chicago	USA
3	136	683.24	0.08	2	delivered	2023-02-15	2023-07-10	Los Angeles	USA

`order_items` 496 rows

id INTEGER (PK) · order_id INTEGER · product_id INTEGER · quantity INTEGER · unit_price REAL · subtotal REAL

First 3 rows of the order_items table
id	order_id	product_id	quantity	unit_price	subtotal
1	1	76	5	192.21	961.05
2	1	82	3	107.1	321.3
3	1	65	1	5.39	5.39

The first rows of each table are shown. See how all the tables relate on the database schema page.

Expected output

Expected output of the challenge
id	user_id	SUM(oi.quantity)
1	43	9
2	8	1
3	136	5
4	115	5
5	140	15

First 5 of 200 rows. This is the output of the model solution on the practice database. The task does not fix a sort order, so your rows may come back in a different order.

What you practise

Summarising many rows into one value with COUNT, SUM, AVG, MIN or MAX
Grouping by more than one column to get a row per combination
Combining rows from two tables on a matching key with an inner join

Hint and approach

Try the challenge first. Open these only when you need them.

Show the hint

JOIN + GROUP BY

Show the step-by-step approach

How the model solution is built, in the order the database evaluates it:

Read from the orders table.
Join order_items on orders.id = order_items.order_id.
Group the rows by orders.id and orders.user_id, so the result has one row for each combination.
For each group, compute SUM(order_items.quantity).

Model solution

Checking your session…

What to watch for

INNER JOIN. An inner join drops rows that have no partner on the other side, and it repeats a row once for every match, which inflates SUM and COUNT.
GROUP BY on several columns. Each extra GROUP BY column splits the groups further. Grouping by a column that is unique per row gives back one group per row.
Aggregate functions. Aggregates skip NULLs. AVG divides by the number of non-NULL values and COUNT(column) can be smaller than COUNT(*).