Query for order number of customer contacts 1

[rawkin]

Hi,

I'm a little bit stumped trying to put together a query that will list a customer Id, and the number of times their Id appears in the History table.

I'm using two tables (simplified);
Customers
id
Name

History
id
CustomerId

I tried the following query;

SELECT a.id,count(a.id) CONTACTS
FROM Customers a, History b
WHERE (a.id = b.CustomerId)
GROUP BY b.CustomerId
ORDER BY CONTACTS;

This works fine, however it doesn't display customers with no entries in the History table -- I need customers with no contacts to show up with '0'.

Any thoughts?

thanks.

 

[TonyGroves]

How about:
[tt]
SELECT c.id,COUNT(h.id)
FROM
customers c
LEFT JOIN history h ON (c.id=h.customerid)
GROUP BY c.id
[/tt]

 

[TonyGroves]

Sorry, that should have been:
[tt]
SELECT c.id,COUNT(h.id) contacts
FROM
customers c
LEFT JOIN history h ON (c.id=h.customerid)
GROUP BY c.id
ORDER BY contacts
[/tt]