SQL: Get count of rows returned from a left join

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.*, C开发者_运维百科OUNT(*) 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?

---

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.

---

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)

---

How about:

SELECT C.*, (SELECT COUNT(*) FROM events E WHERE E.calendar=C.ID) as NumEvents
FROM calendars C

---

try this:

SELECT C.*,
    (
        SELECT COUNT(*)
        FROM [events] AS E
        WHERE E.calendar = C.ID) AS [events]
FROM [calendars] AS C;