Query returns one row with complete sum instead of per-row sum

Question

I have 9 items in a problem_categories tables that have category_id = 1

When I do this query:

select problems.problem_id , problem_title , sum( vote ) as totalVotes 
from problems 
left join problem_votes on problems.problem_id = problem_votes.problem_id 
left join problem_categories on problems.problem_id = problem_categories.problem_id  
where problem_categories.category_id = 1; 

I get 1 row with a complete sum of all the votes for the 9 items. But what I was really looking for was the 9 rows with a vote sum for each. Any idea what is wrong with my query just by looking at it?

My tables are

problem - lists problem information
problem_votes - has a record per vote for each problem
problem_categories - table keeping a problem_id and a category_id so that a problem can be in a certain category

Thanks, Alex

Answer 1

You need to tell MySQL what you're grouping by. Right now it thinks you want EVERYTHING grouped into one row. If you want it to by grouped by problem_title, then add in this line after your WHERE:

GROUP BY problem_title

This will cause you to get a different row for each unique problem_title, and the sum will only count records matching that title.

Edit:

So the whole query will look something like this:

select problems.problem_id , problem_title , sum( vote ) as totalVotes 
from problems 
left join problem_votes on problems.problem_id = problem_votes.problem_id 
left join problem_categories on problems.problem_id = problem_categories.problem_id  
where problem_categories.category_id = 1
group by problem_title; 

Answer 2

Just an FYI, most versions of SQL do not accept group by statements that do not include all non-aggregate columns. So if you're ever working with some other SQL software, you'll likely need to include problems.problem_id and problem_title.

For more on this issue, see this thread: Do all columns in a SELECT list have to appear in a GROUP BY clause