CODEWITHSUNDEEP
sqlsqlitecountleft-join
David Kane
David Kane

Reputation: 91

SQLite: How to return count of zero from left join?

I have two tables: players (id, name) and goals (id, player_id). I want to return a list of all players and the number of goals they have associated with them, even if they have no goals associated with them. I have tried:

SELECT player.name AS player_name, 
       COUNT(goal.id) AS goals 
  FROM player 
       LEFT JOIN goal 
       ON player.id = goal.player_id
 GROUP BY player 
 ORDER BY goals DESC;

But the problem is that it only returns one player with zero goals, and I can't figure out why. I know it's something to do with the GROUP BY clause. There are definitely many players with zero goals.

Upvotes: 0

Views: 544

Answers (2)

nvioli
nvioli

Reputation: 4219

You need to group by player.name. I'm surprised you're not getting an error that player is not a defined column.

Upvotes: 3

PermaFrost
PermaFrost

Reputation: 1446

You are grouping by the entire table, instead of single field in the player table that is unique (such as player.name perhaps).

Upvotes: 3

Related Questions