I have 2 table, one team and one match.

Team:

ID is a Primary key

Matchs

ID_team_home and ID_team_away are foreign keys. And the results i am aiming for is ONE query that doesn't create a table but just select a sum of all of the goals of the teams

please help

I have tried many solutions, i have used sum,join,case when,if,subqueries nothing worked please help. Most of the time it just sum the two rows and give me a totally unaccurate answer.

SELECT team.team_name, SUM(matchs.goal_team_home) as BPe, CASE WHEN matchs.ID_team_home=team.id THEN SUM(matchs.goal_team_home) WHEN matchs.ID_team_away=equipe.id THEN SUM(matchs.goal_team_away) END as test from matchs,team WHERE matchs.ID_team_home=team.id or matchs.ID_team_away=team.id GROUP BY equipe.Equipes
ORDER BY test

CodePudding user response：

We can use a union approach combined with an outer aggregation:

SELECT t1.Team_name, SUM(t2.goal_team) AS Team_goals
FROM Team t1
INNER JOIN
(
    SELECT ID_team_home AS ID_team, goal_team_home AS goal_team FROM Matches
    UNION ALL
    SELECT ID_team_away, goal_team_away FROM Matches
) t2
    ON t2.ID_team = t1.ID
GROUP BY t1.Team_name
ORDER BY t1.Team_name;

The union brings all teams/goals inline into just two columns. We then aggregate by team on that intermediate result to get the team goals.

CodePudding user response：

Join team to matchs with a LEFT join (just in case there is a team without any row in matchs) and use conditional aggregation:

SELECT t.Team_name,
       TOTAL(CASE WHEN t.ID = m.ID_team_home THEN m.goal_team_home ELSE m.goal_team_away END) Team_goals
FROM team t LEFT JOIN matchs m
ON t.ID IN (m.ID_team_home, m.ID_team_away)
GROUP BY t.ID
ORDER BY Team_goals;

See the demo.