user081608 user081608 - 1 month ago 14
MySQL Question

MYSQL SUM() on multiple rows by value

I am working with a database that involves nfl sports teams and am trying to sort data by amount of touchdowns scored by certain defined teams in a given year. I want to show the sum of touchdowns in that year by the two teams defined below. my desired results look like this:

SUM(player.passing_tds) | player.team
21 | NYG
37 | PHI


Here is what I am actually getting:

SUM(player.passing_tds) | player.team
58 | PHI


Here is my SQL:

SELECT SUM(player.passing_tds), player.team
FROM `player`
LEFT JOIN game ON game.g_id = player.g_id
WHERE (team = "NYG" || team = "PHI") && game.season_year = 2013
//WHERE team in ("NYG","PHI") && game.season_year = 2013


I tried both the different WHERE's and give the same results. It looks as if it is just adding them both up and giving the higher team. How would I SUM them up individually?

Answer

You are missing the group by clause:

SELECT SUM(player.passing_tds), player.team 
FROM `player`
     JOIN game ON game.g_id = player.g_id 
WHERE team in ('NYG','PHI') AND game.season_year = 2013
GROUP BY player.team

Given your where criteria, you are negating the outer join so this just removed it.