SQL: Get count of rows returned from a left join

Question

I have two tables, one called calendars and the other one called events. There can be multiple calendars, and multiple events in a calendar. I want to select every calendar, also getting the number of events in the calendar.

This is what I have :

SELECT C.*, COUNT(*) AS events FROM `calendars` AS C 
    LEFT JOIN `events` E ON C.ID=E.calendar 
    GROUP BY C.ID

But that doesn't work. Items with no events still return 1. Any ideas?

Answer 1

You need to use

COUNT(E.calendar)

On rows with no matches the value of this will be NULL and NULL values are not counted.

Answer 2

The problem is that you´re counting rows, and a calendar that has no events, still have one row of his calendar.

Try this:

SUM(case when e.Calendar is null then 0 else 1 end)