CODEWITHSUNDEEP
mysqlsqljoingroup-by
Rawr
Rawr

Reputation: 2224

Query to find how many customers have made an order

So I have two tables one is customer and the other is order. The customer table has a region field. Customers can have multiple orders or no orders.

I tried this:

SELECT customer.region, count(*) 
  FROM customer 
  LEFT JOIN order ON order.cid = customer.id 
 GROUP BY customer.region 
 ORDER BY count(*) DESC

Unfortunately this doesn't exclude customers with no orders and also counts customers multiple times if they have multiple orders.

Upvotes: 0

Views: 2130

Answers (2)

legohead
legohead

Reputation: 540

Have you tried something like

SELECT region, count(DISTINCT customer.id) 
FROM customer 
INNER JOIN order ON order.cid = customer.id 
GROUP BY region

Essentially the INNER join only returns customers who have placed an order and the DISTINCT will only bring back a customer.id once so if a customer has placed multiple orders this will just bring back their customer.id once.

As Michael Y rightly said if you want to bring back all the regions regardless of if they have any matching orders then replace the INNER with a LEFT

Upvotes: 1

Michael Y.
Michael Y.

Reputation: 661

LEFT JOIN if you want to see the regions with 0 orders as well (regions with most orders on top):

SELECT region, count(DISTINCT customer.id) 
FROM customer 
  LEFT OUTER JOIN order ON order.cid = customer.id 
GROUP BY region
ORDER BY 2 desc

Upvotes: 0

Related Questions