Recursive query to get totals by distinct item from a row of a data-CodePudding

I have SQL data that looks like the following:

ResultId    HomeTeam    HomeScore   AwayTeam    AwayScore
1045        USA         2           France      3
1046        USA         1           Botswana    1
1047        Botswana    2           France      2

How can I recurse through this data to get total wins, draws, losses by team for both home and away? My output would look something like:

Team        Wins        Draws      Losses
USA         0           1          1
France      1           1          0
Botswana    0           2          0

I was able to get this done in a very linear way by simply get a list of distinct teams and for each team looping through the data and tallying the win, draw, loss total and aggregating that into variables. However, I imagine there may be a different way to accomplish this.

CodePudding user response：

You don't need recursion here, you can just unpivot the Home and Away sides, then group by the Team and do conditional aggregation

SELECT
  v.Team,
  Wins   = COUNT(CASE WHEN ScoreDiff > 0 THEN 1 END),
  Draws  = COUNT(CASE WHEN ScoreDiff = 0 THEN 1 END),
  Losses = COUNT(CASE WHEN ScoreDiff < 0 THEN 1 END) 
FROM Matches
CROSS APPLY (VALUES
    (HomeTeam, HomeScore - AwayScore),
    (AwayTeam, AwayScore - HomeScore)
) v(Team, ScoreDiff)
GROUP BY
  v.Team;

Team	Wins	Draws	Losses
Botswana	0	1	0
DRC	0	1	0
France	1	1	0
USA	0	1	1

db<>fiddle

CodePudding user response：

There is a more straightforward solution by combining outputs of two queries using Union All, where the first query processes the results of matches for "HomeTeam", and the second for "AwayTeam".

Select Team, Sum(Wins), Sum(Draws), Sum(Losses)
From (
Select HomeTeam As Team,
       Sum(Iif(HomeScore>AwayScore, 1, 0)) As Wins,
       Sum(Iif(HomeScore=AwayScore, 1, 0)) As Draws,
       Sum(Iif(HomeScore<AwayScore, 1, 0)) As Losses
From Tbl
Group by HomeTeam
Union All
Select AwayTeam As Team,
       Sum(Iif(AwayScore>HomeScore, 1, 0)) As Wins,
       Sum(Iif(AwayScore=HomeScore, 1, 0)) As Draws,
       Sum(Iif(AwayScore<HomeScore, 1, 0)) As Losses
From Tbl
Group by AwayTeam) As T
Group By Team
Order by Team