CODEWITHSUNDEEP
sql-serverjoin
Fahad
Fahad

Reputation: 99

Issue with Table Join with group by clause

I have two tables Match_Id and Score . Sample Data

Match_Id

> ID | HomeTeam  |AwayTeam  
> 1    India      Srilanka
  2    Srilanka   India 
  3    Pakistan   India

Score

Match_Id  Team    Score
 1        India    1
 1        India    1
 1       Srilanka  1
 3       Pakistan  1
 2        India    1
 1        India    1

I need to write a query that will give me

1  India   (3) Srilanka (1)
2 Srilanka (0) India  (1)
3 Pakistan (1) India  (0)

Thank you !

Upvotes: 0

Views: 53

Answers (1)

Ian Preston
Ian Preston

Reputation: 39566

select m.ID
  , m.HomeTeam
  , HomeTeamScore = sum(case when m.HomeTeam = s.Team then 1 else 0 end)
  , m.AwayTeam
  , AwayTeamScore = sum(case when m.AwayTeam = s.Team then 1 else 0 end)
from Match_id m
  inner join Score s on m.ID = s.Match_Id
group by m.ID
  , m.HomeTeam
  , m.AwayTeam
order by m.ID

SQL Fiddle with demo.

Upvotes: 3

Related Questions