Count After a Join
Part of the Connected Data section of Coddy's PostgreSQL journey. Lesson 3 of 25.
After a left join, an unmatched customer still contributes one joined row. COUNT(*) counts that placeholder. Count a non-null right-side key instead to count actual matches.
Input table customers:
id | name | city |
|---|---|---|
1 | Ari | Haifa |
2 | Bo | Haifa |
3 | Cleo | Eilat |
4 | Dee | Eilat |
Input table orders:
id | customer_id | amount | ordered_on | status | meta |
|---|---|---|---|---|---|
11 | 1 | 20 | 2025-01-02 | paid | {"channel":"web","items":2,"shipping":{"city":"Haifa"}} |
12 | 1 | 30 | 2025-01-04 | paid | {"channel":"app","items":3,"shipping":{"city":"Haifa"}} |
13 | 2 | 30 | 2025-02-01 | pending | {"channel":"web","items":1} |
14 | 3 | 50 | 2025-02-03 | paid | {"channel":"app","items":5,"shipping":{"city":"Eilat"}} |
15 | 2 | 10 | 2025-02-03 | paid | {"channel":"web","items":2} |
SELECT c.id, COUNT(o.id) AS paid_orders FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'paid' GROUP BY c.id ORDER BY c.id;Dee gets zero paid orders. Putting the paid condition in ON restricts matching orders while preserving every customer. Putting it in WHERE would discard the unmatched rows.
Count the right-side key after LEFT JOIN; put match restrictions in ON when preserving all left rows.
Challenge
MediumReturn customer_id and order_count for every customer, counting all orders and retaining zero-order customers. Order by customer id.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in Connected Data
1Connect Tables
Match Rows with JOINKeep Rows with LEFT JOINCount After a JoinFind Missing MatchesCustomer CoveragePractice on your own: PostgreSQL playground