在左连接查询中显示0结果

Question

How do I count the number of participants in the event_participants table and display also the event_title with zero results. This is my table

event_title
id | name  
1  | New York 
2  | Canada   


event_participants

id | event_title_id | name
1  | 1              | Jon
2  | 1              | Mike

This is my query

SELECT count( * ) AS count
FROM event_participants AS t1
LEFT JOIN event_title AS t2 ON t1.event_title_id = t2.id
GROUP BY t1.event_title_id

I am getting a correct result but does not display the 0 results. I should be getting a result like this

New York | 2
Canada   | 0

Answer 1

Your group by is the problem. you are grouping by the event_title_id where you dont have the id 2.

What you should do is a group by the name of the first table.

SELECT count( * ) AS count
FROM event_participants AS t1
LEFT JOIN event_title AS t2 ON t1.event_title_id = t2.id
GROUP BY t2.name

should work