MySQL count with multiple joins and group by

Question

I can't seem to figure out the proper way to structure this query, and I know there's a way to do it with pure SQL and I would rather do it via a proper query than in my application logic.

I have three tables:

event_types, booking_types, and bookings

What I want is to get the # of bookings for a given event, but include rows for event types that have zero bookings too.

SELECT et.name AS test_type, COUNT(et.name) AS num_seats FROM event_types AS et
LEFT OUTER JOIN booking_types AS bt ON et.type_id = bt.event_type_id
LEFT OUTER JOIN bookings AS b ON b.booking_id = bt.booking_id
WHERE b.event_id = 5
GROUP BY test_type

This query returns something like

test_type | num_seats
----------------------
Transcript |     4
----------------------
Written    |     1
----------------------

There are currently three event_types: 'Transcript','Written', and 'Skill'

Since there are not yet any 'Skill' bookings (a booking can have multiple types) I want the results to include a row like 'Skill' | 0. Also if there are no bookings, it should have 3 zero rows, one for each event_type in this case.

Thank you for any help you can provide!

Taryn · Accepted Answer · 2013-01-10 17:06:50Z

2

Try moving the WHERE filter to the JOIN:

SELECT et.name AS test_type, 
    COUNT(b.booking_id) AS num_seats 
FROM event_types AS et
LEFT OUTER JOIN booking_types AS bt 
    ON et.type_id = bt.event_type_id
LEFT OUTER JOIN bookings AS b 
    ON b.booking_id = bt.booking_id
    AND b.event_id = 5
GROUP BY test_type

See SQL Fiddle with Demo

edited Jan 10, 2013 at 17:06

answered Jan 10, 2013 at 16:55

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Austen Cameron Over a year ago

With you AND the edit mentioned above in the COUNT() clause, it works! I had tried moving the WHERE into the join, but not changing what I was counting. Thank you everyone for the help!

Collectives™ on Stack Overflow

MySQL count with multiple joins and group by

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related