CODEWITHSUNDEEP
mysql
NewProger
NewProger

Reputation: 3075

Mysql query, Select all clients and their orders

Good day everyone. A friend of mine who doesn't speak english asked me to make a question on this site for him.

Okay. His probles is: He needs to make a MySQL query to select all clients and number of their orders, or 0 if they have none.

There are two tables:

table Customers: id, name

table Orders: id, customer_id

Something like this:

client 0, 10 orders
clietn 1, 0 orders
client 2, 3 orders

And so on. But of course without text, just ordinary mysql select result.

Upvotes: 0

Views: 470

Answers (2)

gbn
gbn

Reputation: 432190

This works because "no orders" gives NULL for Orders.id because of the LEFT JOIN.
COUNT(column) ignores NULLs so you'll get zero

SELECT
   C.Name,
   COUNT(O.id)
FROM
   Customers C
   LEFT JOIN
   Orders O ON C.id = O.customer_id
GROUP BY
   C.Name

Upvotes: 1

Eljakim
Eljakim

Reputation: 6937

The following will do as you asked:

select customers.name, count(orders.id) 
from customers 
left join orders on customers.id=orders.customer_id 
group by customers.name

It basically counts the number of orders it can find for each customer.

Upvotes: 1

Related Questions