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
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With