Question 21344 in Mysql Question posted in
The official MySQL documentation can be found here.

How to select two columns(foreign key) value and make its match with a primary key (they have the same primary key) MYSQL

I have 2 table, one team and one match.

Team:

ID Team_name
1 PSG
2 OM

ID is a Primary key

Matchs

ID_team_home ID_team_away goal_team_home goal_team_away
1 2 5 4
2 1 6 1

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

Team_name Team_goals
PSG 6
OM 10

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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,
       SUM(
         CASE t.ID 
           WHEN m.ID_team_home THEN m.goal_team_home 
           WHEN m.ID_team_away THEN m.goal_team_away
           ELSE 0 
         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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search