Count Problem

[dfenton21]

I have the following query:



SELECT
Sessions.SessionID,
Sessions.SessionType,
Sessions.SessionDate,
Count(Bookings.Status) AS BookedPlaces
FROM Sessions
LEFT JOIN Bookings ON Sessions.SessionID = Bookings.SessionID
GROUP BY
Sessions.SessionID,
Sessions.SessionType,
Sessions.SessionDate,

That works perfectly for me. I have four records in the Sessions table. The query gives me the four records and the number of booked places per session (as listed in the Bookings table)

However, I need to change
Count(Bookings.Status) AS BookedPlaces
to count only when Bookings.Status='Active'.

Everything I've tried only counts all the records and not grouped by SessionID, or gives me a syntax error, or says that "at most, one record can be returned from this subquery.

I would appreciate if someone could assist me with this as I've spend the last 1.5 hours trying to figure it out (I have an inkling that the solution is very simple which makes it more frustrating)

Thanks.

[jeffr27]

I'm not an expert by any means, but could you possibly do something like:

SELECT...fields
Count(Bookings.Status) AS BookedPlaces
WHERE Bookings.Status=Active
FROM...
LEFTJOIN...

[dfenton21]

Thanks for your reply.

I tried that already and it gives me a syntax error (missing operator)

[jeffr27]

Is Bookings.Status a yes/no field? If so, try using =yes or true or 1 instead of 'Active'.

[dfenton21]

No, the Status field can contain three possibilities.