Completed Work Orders and Good Units per Line

Easy
255.

Completed Work Orders and Good Units per Line

EasyAggregation

MySQL

The MES (manufacturing execution system) tracks work orders on each assembly line. good_qty is filled in only once an order is completed; it is NULL while an order is planned, running or cancelled.

For every line with at least one completed work order, return line_code, completed_orders (how many of its orders are completed) and good_units (the sum of good_qty over those orders). Lines with no completed order are not listed. Order the rows by line_code.

Tables

Table: WorkOrder

ColumnTypeValues
work_order_idint
line_codevarchar
statusenumplanned, in_progress, completed, cancelled
planned_qtyint
good_qtyint

Primary key: work_order_id.

good_qty is NULL unless status is completed.

Example

The rows must come back in the order shown.

WorkOrder

work_order_idline_codestatusplanned_qtygood_qty
7001L1-ASSYcompleted500488
7002L1-ASSYin_progress400NULL
7003L2-ASSYcompleted300300
7004L3-PAINTcancelled250NULL
7005L1-ASSYcompleted600571
7006L2-ASSYplanned350NULL

Output

line_codecompleted_ordersgood_units
L1-ASSY21059
L2-ASSY1300
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.

Completed Work Orders and Good Units per Line — SQL Problem

The MES (manufacturing execution system) tracks work orders on each assembly line.

  • Difficulty: Easy
  • Topics: Aggregation
  • Dialect: MySQL
  • Problem: #255

Problem statement

The MES (manufacturing execution system) tracks work orders on each assembly line. good_qty is filled in only once an order is completed; it is NULL while an order is planned, running or cancelled.

For every line with at least one completed work order, return line_code, completed_orders (how many of its orders are completed) and good_units (the sum of good_qty over those orders). Lines with no completed order are not listed. Order the rows by line_code.

Tables

Table: WorkOrder

ColumnType
work_order_idint
line_codevarchar
statusenum(planned, in_progress, completed, cancelled)
planned_qtyint
good_qtyint

Primary key: work_order_id.

good_qty is NULL unless status is completed.

Examples

Example 1

WorkOrder

work_order_idline_codestatusplanned_qtygood_qty
7001L1-ASSYcompleted500488
7002L1-ASSYin_progress400NULL
7003L2-ASSYcompleted300300
7004L3-PAINTcancelled250NULL
7005L1-ASSYcompleted600571
7006L2-ASSYplanned350NULL

Output

line_codecompleted_ordersgood_units
L1-ASSY21059
L2-ASSY1300

How to solve Completed Work Orders and Good Units per Line

Both numbers are about completed orders only, so the WHERE clause keeps status = 'completed' rows before grouping. Grouping by line_code then gives one group per line that still has rows: COUNT(*) is the number of completed orders and SUM(good_qty) their good units. A line whose orders are all planned or cancelled has no rows after the filter and therefore no group — exactly the "not listed" rule, with no HAVING needed.

The alternative keeps every row and counts conditionally, then uses HAVING to drop lines with zero completed orders. It works because good_qty is NULL on every non-completed order and SUM ignores NULLs — but it leans on that data rule, while the filter-first version does not, which is why filtering first is the cleaner habit. It also lets an index on status skip most of the table.

Reference solution (MySQL)

SELECT line_code, COUNT(*) AS completed_orders, SUM(good_qty) AS good_units
FROM WorkOrder
WHERE status = 'completed'
GROUP BY line_code
ORDER BY line_code

Another way

SELECT line_code, SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_orders, SUM(good_qty) AS good_units
FROM WorkOrder GROUP BY line_code
HAVING SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) > 0
ORDER BY line_code

← Solar Plant Monthly Generation in 2024 · Machines With Over Two Hours of Unplanned Downtime →