Conditional Count of Related Records

Question

I'm sure there must be a simple way to do this, but I've been tearing my hair out for hours now and I'm getting nowhere. Here is a working query from a customer listing utility:

SELECT c.customer_ID, title, surname, forenames, COUNT(booking_ID) AS bookings
FROM customer c 
LEFT JOIN booking b 
ON c.customer_ID = b.customer_ID
WHERE customer_Live
GROUP BY c.customer_ID, surname, forenames, title
ORDER BY surname;

Here is the problem: the COUNT returns all of the related bookings. But the booking table has a 'booking_Live' column which is set to false whenever a booking is cancelled. What I need to do is somehow exclude cancelled bookings from the count; so if all a customer has is cancelled bookings, it will return 0. I've tried putting a HAVING clause on the group, but that just ends up removing any customers with zero live booking from the output.

Answer 1

A subquery should resolve your issue. Something like this.

SELECT c.customer_ID, title, surname, forenames, COUNT(booking_ID) AS bookings
FROM customer c 
LEFT JOIN (SELECT customer_ID, booking_id FROM Booking WHERE booking_live = true) as b 
ON c.customer_ID = b.customer_ID
WHERE customer_Live
GROUP BY c.customer_ID, surname, forenames, title
ORDER BY surname;

Answer 2

Maybe I didn't get it well but why don't you use:

SELECT c.customer_ID, title, surname, forenames, COUNT(booking_ID) AS bookings
FROM customer c 
LEFT JOIN booking b 
ON (c.customer_ID = b.customer_ID AND b.booking_Live = true)
WHERE customer_Live
GROUP BY c.customer_ID, surname, forenames, title
ORDER BY surname;