Packing Slip Item List for Each Quick Order

Medium
162.

Packing Slip Item List for Each Quick Order

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

ColumnType
order_idint
store_idint
ordered_atdatetime

Primary key: order_id.

One row per grocery order.

Table: OrderItem

ColumnType
order_idint
product_namevarchar
quantityint

Primary key (together): order_id, product_name.

One row per product in an order; order_id is always in QuickOrder.

Example

The rows must come back in the order shown.

QuickOrder

order_idstore_idordered_at
30132024-11-05 07:40:00
30212024-11-05 07:42:00
30332024-11-05 08:05:00
30432024-11-05 08:30:00

OrderItem

order_idproduct_namequantity
301Toned Milk 500ml2
301Brown Bread1
301Amul Butter1
302Basmati Rice 1kg1
303Banana Robusta6
303Eggs 6pc1

Output

order_iditemstotal_units
301Amul Butter x1, Brown Bread x1, Toned Milk 500ml x24
303Banana Robusta x6, Eggs 6pc x17
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
order_idint
store_idint
ordered_atdatetime

Primary key: order_id.

One row per grocery order.

Table: OrderItem

ColumnType
order_idint
product_namevarchar
quantityint

Primary key: order_id, product_name.

One row per product in an order; order_id is always in QuickOrder.

Examples

Example 1

QuickOrder

order_idstore_idordered_at
30132024-11-05 07:40:00
30212024-11-05 07:42:00
30332024-11-05 08:05:00
30432024-11-05 08:30:00

OrderItem

order_idproduct_namequantity
301Toned Milk 500ml2
301Brown Bread1
301Amul Butter1
302Basmati Rice 1kg1
303Banana Robusta6
303Eggs 6pc1

Output

order_iditemstotal_units
301Amul Butter x1, Brown Bread x1, Toned Milk 500ml x24
303Banana Robusta x6, Eggs 6pc x17

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_id

Another 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 →