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;