Reputation: 3227
I have this sql:
SELECT
c.id,
c.name,
SUM(CASE WHEN o.status = 1 THEN 1 ELSE 0 END)
FROM orders o, customers c
WHERE o.customer_id = c.id
GROUP BY c.id;
Take all customers from table customer
.
Count how much rows have column status=1
in table orders
, grouped by customer_id
.
My sql
works perfect if customer have at least one order in table orders
(any status, even if all have status different by 1
).
But if there is an customer which have no order(does not depend status) in table order
, it will not appear in result.
Let's say we have those 2 customers:
100 - C1 - 15 orders(7 have status 1)
101 - C2 - No orders
The Result will be:
100 | C1 | 7
How I can include C2
?
100 | C1 | 7
101 | C2 | 0
Upvotes: 1
Views: 188
Reputation: 312219
You need a left join:
SELECT id, name, COALESCE(sum_orders, 0)
FROM customer c
LEFT JOIN (SELECT customer_id, SUM(CASE WHEN o.status = 1 THEN 1 ELSE 0 END) AS sum_orders
FROM orders
GROUP BY customer_id) o ON c.id = o.customer_id
Upvotes: 0
Reputation: 1271051
Never use commas in the FROM
clause. Always use proper, explicit, standard JOIN
syntax.
And, you want a LEFT JOIN
:
SELECT c.id, c.name,
COUNT(o.customer_id)
FROM customers c LEFT JOIN
orders o
ON o.customer_id = c.id AND o.status = 1
GROUP BY c.id;
Upvotes: 2