Menu
CoddyTech

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:

idnamecity
1AriHaifa
2BoHaifa
3CleoEilat
4DeeEilat

Input table orders:

idcustomer_idamountordered_onstatusmeta
111202025-01-02paid{"channel":"web","items":2,"shipping":{"city":"Haifa"}}
121302025-01-04paid{"channel":"app","items":3,"shipping":{"city":"Haifa"}}
132302025-02-01pending{"channel":"web","items":1}
143502025-02-03paid{"channel":"app","items":5,"shipping":{"city":"Eilat"}}
152102025-02-03paid{"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 icon

Challenge

Medium

Return 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.
quiz iconTest yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in Connected Data

Practice on your own: PostgreSQL playground