Is there a way to filter and arrange data in SQL for a particular entry?

January 29, 2023

I have the following two tables

TeamsTable

TeamID	TeamName
1	Name1
2	Name2
3	Name3
…	…

and

GameScoresTable

GameID	HomeTeam(FK)	AwayTeam(FK)	HomeTeamScore	AwayTeamScore
1	1	2	50	70
2	1	3	70	80
3	2	1	70	80
1	2	3	50	60
2	3	1	70	80
3	3	2	40	90
…	…	…	…	…

I am trying to get a table where I receive the top 5 offensive teams, ie, the teams that has scored the most points in all their matches combined (in desc order).

For example, the team that has scored the most points in all their matches, both as home or away is team Name2 with 300 pts. They should be first (in the limited example).

FinalTable

TeamName	TotalScore
Name2	300
Name1	280
Name3	250
…	…

I don’t know if it is even possible with sql queries alone or if I have to get the data for all games and then process it separately.

>Solution :

We can try the following union approach:

SELECT t1.TeamName, SUM(t2.Score) AS TotalScore
FROM TeamsTable t1
INNER JOIN
(
    SELECT HomeTeam AS Team, HomeTeamScore AS Score FROM GameScoresTable
    UNION ALL
    SELECT AwayTeam, AwayTeamScore FROM GameScoresTable
) t2
    ON t2.Team = t1.TeamID
GROUP BY
    t1.TeamName;

If you want to find the top 5 total scores, then we can place the above query into a CTE, and use RANK:

WITH cte AS (
    SELECT t1.TeamName, SUM(t2.Score) AS TotalScore,
           RANK() OVER (ORDER BY SUM(t2.Score) DESC) rnk
    FROM TeamsTable t1
    INNER JOIN
    (
        SELECT HomeTeam AS Team, HomeTeamScore AS Score FROM GameScoresTable
        UNION ALL
        SELECT AwayTeam, AwayTeamScore FROM GameScoresTable
    ) t2
        ON t2.Team = t1.TeamID
    GROUP BY t1.TeamName
)

SELECT TeamName, TotalScore
FROM cte
WHERE rnk <= 5
ORDER BY TotalScore DESC;