user081608 user081608 - 3 years ago 174
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) |
21 | NYG
37 | PHI

Here is what I am actually getting:

SUM(player.passing_tds) |
58 | PHI

Here is my SQL:

SELECT SUM(player.passing_tds),
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 Source

You are missing the group by clause:

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

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download