Menu
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?
443
The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.
COUNT(*) or COUNT(1) The seemingly obvious way to get the count of rows from the table is to use the COUNT function. There are two common ways to do this – COUNT(*) and COUNT(1).
To counts all of the rows in a table, whether they contain NULL values or not, use COUNT(*). That form of the COUNT() function basically returns the number of rows in a result set returned by a SELECT statement.
The LEFT JOIN is a clause of the SELECT statement. The LEFT JOIN clause allows you to query data from multiple tables. The LEFT JOIN returns all rows from the left table and the matching rows from the right table. If no matching rows are found in the right table, NULL are used.
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.
96
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
