I have a table players with an id and a name row. I also have a possessions table with a foreign player_id and an amount of things they own. I want to list the possessions of all players. Since some players do not have possessions, I thought the right tool would be a left join (the players being left):
SELECT players.nickname, possessions.amount
FROM players LEFT JOIN possessions ON possessions.player_id = players.id
Which looks right (foo owns several different things, bar doesn’t own anything):
Now I want to sum the amount. I tried both SUM() and TOTAL(), both filter out bar. I tried forcing the NULL to a 0 with coalesce and some CASE statements. But the "bar" player disappears every time:
SELECT players.nickname, total(possessions.amount)
FROM players LEFT JOIN possessions ON possessions.player_id = players.id
My assumption is that things get filtered by the ON clause maybe? But it seems to go through in the above results, so I don’t understand.
TL;DR: I want "bar" listed with 0 amount, it doesn’t appear in the right table.
>Solution :
You want to use COALESCE() here, to report the sum as zero for any nickname having no possessions. But, your query has another problem too, and should be using GROUP BY:
SELECT p.nickname, COALESCE(SUM(po.amount), 0) AS amount
FROM players p
LEFT JOIN possessions po ON po.player_id = p.id
GROUP BY p.nickname;