CODEWITHSUNDEEP
sql
Ai-Lien Bui
Ai-Lien Bui

Reputation: 11

Missing 0 value when using SUM(CASE WHEN) in SQL

I used the query below to show summary table of score of all matches. The table game had id, mdate, stadium, team1, team2. The table goal has matchid, player name, teamid, gtime so it records every single goal.

SELECT mdate,
  team1,
  sum(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) as score1,
  team2,
sum(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) as score2
  FROM game JOIN goal ON matchid = id
GROUP BY mdate, team1, team2
ORDER BY mdate, matchid, team1, team2

The reuslt of my query was missing 2 cases, in which both team score 0.

Please help advise if my syntax was okay.

Upvotes: 1

Views: 1883

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

You should be using a LEFT JOIN when there may be no rows in the goal table for a particular game, but you still want the game row to produce a result row:

SELECT mdate,
  team1,
  sum(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) as score1,
  team2,
sum(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) as score2
  FROM game LEFT JOIN goal ON matchid = id
GROUP BY mdate, team1, team2
ORDER BY mdate, matchid, team1, team2

This will produce NULLs rather than 0s when there are no goals to count, but that can be fixed by wrapping the SUM expressions with a COALESCE, if it's important that the result be 0.

Upvotes: 3

Related Questions