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
| Column | Type |
|---|---|
| work_order_id | int |
| line_code | varchar |
| status | enum(planned, in_progress, completed, cancelled) |
| planned_qty | int |
| good_qty | int |
Primary key: work_order_id.
good_qty is NULL unless status is completed.
Examples
Example 1
WorkOrder
| work_order_id | line_code | status | planned_qty | good_qty |
|---|---|---|---|---|
| 7001 | L1-ASSY | completed | 500 | 488 |
| 7002 | L1-ASSY | in_progress | 400 | NULL |
| 7003 | L2-ASSY | completed | 300 | 300 |
| 7004 | L3-PAINT | cancelled | 250 | NULL |
| 7005 | L1-ASSY | completed | 600 | 571 |
| 7006 | L2-ASSY | planned | 350 | NULL |
Output
| line_code | completed_orders | good_units |
|---|---|---|
| L1-ASSY | 2 | 1059 |
| L2-ASSY | 1 | 300 |
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_codeAnother 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 →