Skip to content
Quantoros

How do you get a count of customers including those with 0 orders?

SQLLevel: IntermediateJOINs Deep Dive

How do you get a count of customers including those with 0 orders?

Hints
  • Define one output row and the source-table keys.
  • Check duplicates, NULLs and the order of filtering and aggregation.

Answer

LEFT JOIN combined with COUNT of the right side's column (not *).

SELECT c.customer_id, c.name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

Using COUNT(*) here would be wrong — it would count 1 for customers with no orders (because the row still exists, just with NULLs). COUNT(o.order_id) returns 0 because NULLs aren't counted.

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions