i just started my journey with SQL, and made some tables of Cyclists, and Cycling Teams.
Cyclist’s table contains columns: ID, Name, Team (which is foreign key of TEAMS ID)
Team’s table contains columns: ID, Name, Number of Cyclists
I want to Count number of Cyclists in each team, by using count() function ( Or basically any function, i just want to make it work )
After many minutes i figured out this query:
SELECT teams.name,
count(*) AS NumberOfCyclists FROM cyclists
JOIN teams ON cyclists.team = teams.id
group by teams.name;
and i Achieved this:
Which is all good, but when i LEFT JOIN i achieve:
My question is: How to get all of the teams (there are 15 of them, not 11), even those where the count of the cyclists is 0?
>Solution :
You must count not the amount of rows (COUNT(*)) which cannot be zero but the amount of non-NULL values in definite column (the column which is used in joining condition usage is recommended) taken from right table (COUNT(table.column)). With LEFT JOIN, of course.
But the logic needs teams table to be left. And finally:
SELECT teams.name,
count(cyclists.team) AS NumberOfCyclists
FROM teams
LEFT JOIN cyclists ON cyclists.team = teams.id
group by teams.name;