Count orders per status, showing 0 for statuses that have no orders
Tables statuses(id, name) and orders(id, status_id). Return the order count for every status, including statuses that currently have no orders — those must show a count of 0, not be missing from the result.
-- count of orders per status; statuses with none show 0Write the query.
Drive from statuses with a LEFT JOIN to orders, so an order-less status still yields a row. Use COUNT(o.id) — the joined column, not COUNT(*) — because COUNT over the unmatched side's NULL gives 0, while COUNT(*) counts the padding row as 1.
- ✗Using INNER JOIN, which drops the statuses that have no orders
- ✗Using COUNT(*) on the outer join and counting the padding row as 1
- ✗Assuming an empty scalar subquery returns 0 rather than NULL
- →Why does COUNT(o.id) give 0 while COUNT(*) gives 1 for an empty status?
- →How would you also show total order amount per status in the same query?
Start from statuses so every status is present, LEFT JOIN the orders, and count the joined column.
SELECT s.name,
COUNT(o.id) AS order_count
FROM statuses s
LEFT JOIN orders o ON o.status_id = s.id
GROUP BY s.id, s.name
ORDER BY order_count DESC;The key subtlety is COUNT(o.id) versus COUNT(*). For a status with no orders the outer join produces a single row with all orders columns NULL. COUNT(o.id) ignores that NULL and returns 0; COUNT(*) counts the row itself and wrongly returns 1. An INNER JOIN would drop the empty statuses entirely.
