CODEWITHSUNDEEP
mysqlsqlselectgroup-bycase
jantristanmilan
jantristanmilan

Reputation: 4368

Whats wrong with my query with CASE statement

I'm trying to solve #13 on http://www.sqlzoo.net/wiki/The_JOIN_operation

"List every match with the goals scored by each team as shown. This will use "CASE WHEN" which has not been explained in any previous exercises."

Here's my query:

SELECT game.mdate, game.team1, 
SUM(CASE WHEN goal.teamid = game.team1 THEN 1 ELSE 0 END) AS score1,
game.team2,
SUM(CASE WHEN goal.teamid = game.team2 THEN 1 ELSE 0 END) AS score2

FROM game INNER JOIN goal ON matchid = id
GROUP BY game.id
ORDER BY mdate,matchid,team1,team2

I get the result "Too few rows". I don't understand what part I got wrong.

Upvotes: 9

Views: 15836

Answers (9)

Sergey Ermolaev
Sergey Ermolaev

Reputation: 1

My answer

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

Upvotes: 0

reda Massali
reda Massali

Reputation: 1

In case you are working in SQLZOO

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

Upvotes: 0

YssCa
YssCa

Reputation: 1

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

Upvotes: 0

user6623052
user6623052

Reputation: 31

As there are columns with the same names in both tables, no need to specify where the current column is coming from.

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

Upvotes: 3

Anupam Dewan
Anupam Dewan

Reputation: 51

I Did it using a subquery.

The only trick here is to understand that we need to use Left Join AND not inner join as there are games which have 0 goals scored which do not capture in our query if we use the inner join(the 0-0 case)

Hope below query helps!

select t1.mdate,t1.team1,sum(t1.score1),t1.team2,sum(t1.score2) from(
SELECT mdate,
  team1,
  CASE WHEN teamid=team1 THEN 1 ELSE 0 END score1,
  team2,
  CASE WHEN teamid=team2 THEN 1 ELSE 0 END score2
  FROM game JOIN goal ON matchid = id)t1
group by t1.mdate,t1.team1,t1.team2
order by mdate,team1,team2

Upvotes: 1

mjsqu
mjsqu

Reputation: 5452

An INNER JOIN only returns games where there have been goals, i.e. matches between the goal and game table.

What you need is a LEFT JOIN. You need all the rows from your first table, game but they don't need to match all the rows in goal, as per the 0-0 comment I made on your question:

SELECT game.mdate, game.team1, 
SUM(CASE WHEN goal.teamid = game.team1 THEN 1 ELSE 0 END) AS score1,
game.team2,
SUM(CASE WHEN goal.teamid = game.team2 THEN 1 ELSE 0 END) AS score2

FROM game LEFT JOIN goal ON matchid = id
GROUP BY game.id,game.mdate, game.team1, game.team2 
ORDER BY mdate,matchid,team1,team2

This returns the 0-0 result between Portugal and Spain on 27th June, which your initial answer missed out.

Upvotes: 32

Hai Wang
Hai Wang

Reputation: 1

`

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

`

Upvotes: -1

Xindong Hao
Xindong Hao

Reputation: 11

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

Upvotes: 1

cybork
cybork

Reputation: 587

For MySQL the answer by @mjsqu works, but for SQL Server and some other dialects you need to specify all the columns in the GROUP BY. So if you're familiair with MySQL, remember that in some dialects you have to be more specific in the GROUP BY (unfortunately...). Beneath a script that works for SQL Server:

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

Upvotes: 0

Related Questions