Packing Slip Item List for Each Quick Order — SQL Problem
Pickers at a dark store read one line per order from the packing screen.
- Difficulty: Medium
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #162
Problem statement
Pickers at a dark store read one line per order from the packing screen. For each order placed at store 3, build the line from its items: each item written as <product_name> x<quantity> (e.g. Amul Butter x2), items sorted by product_name alphabetically and joined with , (a comma and a space).
Return order_id, items and total_units (the sum of quantities) for every store-3 order that has at least one item, ordered by order_id.
Tables
Table: QuickOrder
| Column | Type |
|---|---|
| order_id | int |
| store_id | int |
| ordered_at | datetime |
Primary key: order_id.
One row per grocery order.
Table: OrderItem
| Column | Type |
|---|---|
| order_id | int |
| product_name | varchar |
| quantity | int |
Primary key: order_id, product_name.
One row per product in an order; order_id is always in QuickOrder.
Examples
Example 1
QuickOrder
| order_id | store_id | ordered_at |
|---|---|---|
| 301 | 3 | 2024-11-05 07:40:00 |
| 302 | 1 | 2024-11-05 07:42:00 |
| 303 | 3 | 2024-11-05 08:05:00 |
| 304 | 3 | 2024-11-05 08:30:00 |
OrderItem
| order_id | product_name | quantity |
|---|---|---|
| 301 | Toned Milk 500ml | 2 |
| 301 | Brown Bread | 1 |
| 301 | Amul Butter | 1 |
| 302 | Basmati Rice 1kg | 1 |
| 303 | Banana Robusta | 6 |
| 303 | Eggs 6pc | 1 |
Output
| order_id | items | total_units |
|---|---|---|
| 301 | Amul Butter x1, Brown Bread x1, Toned Milk 500ml x2 | 4 |
| 303 | Banana Robusta x6, Eggs 6pc x1 | 7 |
How to solve Packing Slip Item List for Each Quick Order
The output has one row per order, so group the order's items by order_id after keeping only store 3. The aggregate that turns many rows into one string is GROUP_CONCAT: its argument can be any expression, here CONCAT(product_name, ' x', quantity) — the number is turned into text by CONCAT — and it takes its own ORDER BY product_name and SEPARATOR ', '.
The ORDER BY inside GROUP_CONCAT is not decoration: without it the item order is whatever order the rows were read in, and the same order could print differently on two runs. MySQL's default separator is a comma with no space, so the separator must be given.
Orders with no items have no rows to join and drop out, which is what the statement asks for. Building the label in a derived table (here with CONCAT_WS, which puts the separator between its arguments) and concatenating outside is the same plan. One join, one group per order.
Reference solution (MySQL)
SELECT o.order_id,
GROUP_CONCAT(CONCAT(i.product_name, ' x', i.quantity) ORDER BY i.product_name SEPARATOR ', ') AS items,
SUM(i.quantity) AS total_units
FROM QuickOrder o
JOIN OrderItem i ON i.order_id = o.order_id
WHERE o.store_id = 3
GROUP BY o.order_id
ORDER BY o.order_idAnother way
SELECT order_id, GROUP_CONCAT(label ORDER BY product_name SEPARATOR ', ') AS items, SUM(quantity) AS total_units
FROM (SELECT i.order_id, i.product_name, i.quantity, CONCAT_WS(' x', i.product_name, i.quantity) AS label
FROM OrderItem i WHERE i.order_id IN (SELECT order_id FROM QuickOrder WHERE store_id = 3)) t
GROUP BY order_id
ORDER BY order_id← Month-Over-Month Delivered GMV Change by City · Coupon Discount Spend by Campaign Prefix →