Modify SQL query to get all users?

Question

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

u already write DISTINCT (o.customer_id) then what is the need of GROUP BY customer_id — xkeshav
– xkeshav, Commented Apr 7, 2011 at 9:41

Dumitrescu Bogdan · Accepted Answer · 2011-04-07 09:37:21Z

1

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

answered Apr 7, 2011 at 9:37

Dumitrescu Bogdan

7,2652 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lazarus · Accepted Answer · 2011-04-07 09:39:14Z

1

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

answered Apr 7, 2011 at 9:39

Lazarus

43.2k4 gold badges47 silver badges54 bronze badges

Comments

Stephen Chung · Accepted Answer · 2011-04-07 09:45:27Z

0

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

answered Apr 7, 2011 at 9:45

Stephen Chung

14.6k1 gold badge37 silver badges48 bronze badges

Collectives™ on Stack Overflow

Modify SQL query to get all users?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related