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

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.

CodePudding user response：

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;

CodePudding user response：

try this query in T-SQL (SQL Server):

WITH cte AS (
    SELECT 
        TeamsTable.TeamName, 
        SUM(CASE WHEN TeamsTable.TeamID = GameScoresTable.HomeTeam THEN HomeTeamScore ELSE AwayTeamScore END) AS TotalScore
    FROM 
        TeamsTable
        JOIN GameScoresTable ON TeamsTable.TeamID = GameScoresTable.HomeTeam OR TeamsTable.TeamID = GameScoresTable.AwayTeam
    GROUP BY 
        TeamsTable.TeamName
)
SELECT 
    TeamName, TotalScore
FROM 
    cte
ORDER BY 
    TotalScore DESC
OFFSET 0 ROWS 
FETCH NEXT 5 ROWS ONLY;