Count records problem. Display field even when count is zero.

[DrStu]

I have a table tblBookings.

In this table it has a bookingID, CustomerID and some other none relevant details.

The CustomerID comes from table tblCustomer. i.e a customerID must exist in the customer table to be allowed in the bookings table tblBookings

A customer can exist in tblCustomer without existing in the booking table.

I am trying to write a query that will list each and every customer ID in the tblCustomer and count the number of bookings that that customer has (even if it is zero).

I have a query that will count the bookings if they exist in the booking table and display the number of times that a customer appears in the bookings table.

SELECT tblBookings.CustomerID, Count(tblBookings.CustomerID) AS NoOfBookings
FROM tblBookings
GROUP BY tblBookings.CustomerID;


How do I create a query that will do this but list all customers even if they don't exist in the bookings table (but obviously occur in the customers table)

I am trying to create a similar query where all bookings per hotel are listed even if no bookings are made for that hotel. I am guessing the answer is the same as above.

The Ritz. Bookings 0
The Hilton. Bookings 3
The Carlton. Bookings 0
The Lowry. Bookings 2

For every hotel.

That kind of thing.

If you need more information please shout.

 

[KeithG]

In your query you need to use a left join on your tables, with tblCustomer on the left and tblbookings on the right on the CustomerID field.

 

[cardgunner]

Dr Stu,

I am looking for the answer myself to a similiar problem. I need have a 0 count show up as 0 rather then not at all. Have you gotten any answers other then what is posted?

 

[DrStu]

I have a table tblBookings.

Based on postings on other sites and forums it seems that my problem was to do with different types of joins, namely a LEFT JOIN in my code instead of an INNER JOIN.

Having just leaned about the other join types I managed to get my code to work as required

The SQL is

SELECT tblCustomer.CustomerID, Count(tblBookings.CustomerID) AS NoOfBookings
FROM tblCustomer LEFT JOIN tblBookings ON tblCustomer.CustomerID = tblBookings.CustomerID
GROUP BY tblCustomer.CustomerID, tblBookings.CustomerID
ORDER BY tblCustomer.CustomerID;

For a right join the FROM would be FROM tblbookings RIGHT JOIN tblCustomer ON tblCustomer.CustomerID = tblBookings.CustomerID


Hope this is of use to someone else if they have the same problem in the future.