By YX Fulfillment
One line asks for three units, and another asks for one. The pick list needs two instructions. Inventory needs four units. A test made entirely of one-unit lines can let a report swap those two counts without anyone noticing.
Use a synthetic order with two active lines for the same item and a third cancelled line. Assume partial cancellation has already been applied to each eligible line's quantity and that this report represents the current order state.
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE line (
line_id TEXT PRIMARY KEY,
sku TEXT NOT NULL,
qty INTEGER NOT NULL CHECK (qty > 0),
status TEXT NOT NULL CHECK (status IN ('open', 'cancelled'))
);
INSERT INTO line VALUES ('L1','S-9',3,'open'),
('L2','S-9',1,'open'),
('L3','S-8',5,'cancelled');
""")
wrong = db.execute("""
SELECT COUNT(*), COUNT(qty) FROM line WHERE status='open'
""").fetchone()
counts = db.execute("""
SELECT COUNT(*), COALESCE(SUM(qty), 0) FROM line WHERE status='open'
""").fetchone()
assert wrong == (2, 2)
assert wrong != (2, 4)
assert counts == (2, 4)
print("COUNT(qty) implementation:", wrong)
print("Line count and unit count:", counts)
db.execute("UPDATE line SET status='cancelled'")
empty = db.execute("""
SELECT COUNT(*), COALESCE(SUM(qty), 0) FROM line WHERE status='open'
""").fetchone()
assert empty == (0, 0)
print("All lines cancelled:", empty)
Output from a local run on Python 3.9.6
COUNT(qty) implementation: (2, 2)
Line count and unit count: (2, 4)
All lines cancelled: (0, 0)
COUNT(qty) counts rows with a non-NULL quantity. Because the column rejects NULL, it always equals the line count in this fixture. SUM(qty) adds the requested units. Both metrics use the same eligible-row filter, so the five cancelled units contribute to neither. COUNT(DISTINCT sku) would answer a third question and return one here.
If the source is a status history rather than a current-state table, choose exactly one accepted revision per order line before summing. If an order line splits into several warehouse instructions, use the instruction table to measure pick tasks and state clearly whether those quantities are planned, picked, or shipped.
Top comments (0)