Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Adding a join and a count to existing query inflates the sums and count function output
- 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;
- select problems.problem_id , problem_title , sum( vote ) as totalVotes , count(solution_name) 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
- left join suggested_solutions on
- problems.problem_id = suggested_solutions.problem_id
- where problem_categories.category_id = 1 GROUP BY problem_title;
- SELECT problems.problem_id,
- problem_title,
- SUM(vote) AS totalVotes,
- Solution_Count
- 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
- LEFT JOIN (SELECT COUNT(solution_name) AS Solution_Count,
- problem_id
- FROM suggested_solutions
- GROUP BY problem_id) ss
- ON problems.problem_id = ss.problem_id
- WHERE problem_categories.category_id = 1
- GROUP BY problem_title;
Advertisement
Add Comment
Please, Sign In to add comment