CODEWITHSUNDEEP
mysql
John
John

Reputation: 1

Modify SQL query to get all users?

I currently have this query.

SELECT DISTINCT (o.customer_id), count( o.id ) AS orders, c.* 
FROM `order` AS o LEFT JOIN customer AS c ON o.customer_id = c.id 
GROUP BY customer_id

What it does is it returns all customers that have made an order and counts the number of orders each customer has made.

What I need to do is modify this query so it also returns those customers who haven't made an order. Do you have any idea how this would be done?

I tried to reverse the query but this didn't do the trick..

SELECT DISTINCT (o.customer_id), count( o.id ) AS orders, c.* 
FROM customer AS c LEFT JOIN order AS o ON o.customer_id = c.id 
GROUP BY o.customer_id

Upvotes: 0

Views: 126

Answers (3)

Stephen Chung
Stephen Chung

Reputation: 14605

SELECT o.customer_id, c.* 
FROM customer AS c LEFT JOIN order AS o ON o.customer_id = c.id 
WHERE o.id IS NULL
GROUP BY o.customer_id

You can also skip the "GROUP BY" clause because when the orders side is NULL, there is always only one row for the customer:

SELECT o.customer_id, c.* 
FROM customer AS c LEFT JOIN order AS o ON o.customer_id = c.id 
WHERE o.id IS NULL

Upvotes: 0

Lazarus
Lazarus

Reputation: 43074

What about:

SELECT DISTINCT (o.customer_id), count( o.id ) AS orders, c.* 
    FROM `order` AS o 
    LEFT OUTER JOIN customer AS c ON o.customer_id = c.id GROUP BY customer_id

Upvotes: 1

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

Try this.

SELECT o.customer_id, sum( case when o.id is not null then 1 else 0 end ) AS orders, c.* 
FROM customer  c
LEFT JOIN order o ON o.customer_id = c.id GROUP BY customer_id

Upvotes: 1

Related Questions