Skip to content
Advertisement

SQL JOIN tables and and sum columns with the same PK

Here are my tables:

Players

Name DoB Ranking
Ben 01/01/1999 1
Tom 02/01/1999 1
Sam 03/01/1999 1
Sam 03/01/1999 2

Ranking

Ranking Points
1 100
2 50

I want to join the tables and add the total points for each player then output this table.

Output

Name DoB Points
Ben 01/01/1999 100
Tom 02/01/1999 100
Sam 03/01/1999 150

I am using this statement currently:

SELECT Matches.Name, 
       Matches.DoB, 
       Matches.Rankin, 
       SUM(Ranking.Points)
FROM Matches, Ranking
WHERE Matches.Ranking = Ranking.Ranking
GROUP BY Matches.Name

However I am getting this error when I run the code:

SELECT Matches.Name, Matches.Ranking, SUM(Ranking.Points)
FROM Matches, Ranking WHERE Matches.Ranking = Ranking.Ranking GROUP BY
Matches.Name LIMIT 0, 1000  Error Code: 1055. Expression #2 of SELECT
list is not in GROUP BY clause and contains nonaggregated column
'ad.Matches.Ranking' which is not functionally dependent on columns in
GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by 0.00044 sec

New to SQL so any help with this would be much appreciated 🙂

Advertisement

Answer

You have to add all of your field in Select to the group by

SELECT Matches.Name, 
       Matches.DoB, 
       Matches.Rankin, 
       SUM(Ranking.Points)
FROM Matches, Ranking
WHERE Matches.Ranking = Ranking.Ranking
GROUP BY Matches.Name, Matches.DoB, Matches.Rankin
1 People found this is helpful
Advertisement