Hard Query
-
I have 2 tables -Table1 ( CountryID is primary key)
CountryID Country
US America
UK England
IND India
AUS Australia
Table2---- (G1 is number of goals by team1 and G2 by team2)
Team1 Team2 G1 G2
US UK 2 3
US IND 3 5
US Aus 1 1
UK IND 3 2
UK AUS 4 6
IND AUS 2 1
The result should be in the following format
Country NoOfMatchesPlayed Win Loss Draw
India 3 2 1 0
America 3 0 2 1
England 3 2 1 0
Australia 3 1 1 1
Win,Loss,Draw is no of matches win,Lose or played draw by a team.
Any help will be highly appreciated.
Thanks in Advance
Ankur Bhardwaj
-
select Country, count(*) as NoOfMatchesPlayed,
sum(case when MyScore > OppScore then 1 else 0 end) as Win,
sum(case when MyScore < OppScore then 1 else 0 end) as Loss,
sum(case when MyScore = OppScore then 1 else 0 end) as Draw,
from
( select
Team1 as MyTeam, Team2 as MyOpponent, G1 as MyScore, G2 as OppScore
from Table2
union all
Team2 as MyTeam, Team1 as MyOpponent, G2 as MyScore, G1 as OppScore
from Table2
) g
inner join
Table1 c
on c.CountryID = MyTeam
group by Country
Russel Loski, MCSE Business Intelligence, Data Platform
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply