Guest User

Untitled

a guest
May 17th, 2012
24
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.42 KB | None | 0 0
  1. Adding a join and a count to existing query inflates the sums and count function output
  2. select problems.problem_id , problem_title , sum( vote ) as totalVotes
  3. from problems
  4. left join problem_votes on
  5. problems.problem_id = problem_votes.problem_id
  6. left join problem_categories on
  7. problems.problem_id = problem_categories.problem_id
  8. where problem_categories.category_id = 1 GROUP BY problem_title;
  9.  
  10. select problems.problem_id , problem_title , sum( vote ) as totalVotes , count(solution_name) from problems
  11. left join problem_votes on
  12. problems.problem_id = problem_votes.problem_id
  13. left join problem_categories on
  14. problems.problem_id = problem_categories.problem_id
  15. left join suggested_solutions on
  16. problems.problem_id = suggested_solutions.problem_id
  17. where problem_categories.category_id = 1 GROUP BY problem_title;
  18.  
  19. SELECT problems.problem_id,
  20. problem_title,
  21. SUM(vote) AS totalVotes,
  22. Solution_Count
  23. FROM problems
  24. LEFT JOIN problem_votes
  25. ON problems.problem_id = problem_votes.problem_id
  26. LEFT JOIN problem_categories
  27. ON problems.problem_id = problem_categories.problem_id
  28. LEFT JOIN (SELECT COUNT(solution_name) AS Solution_Count,
  29. problem_id
  30. FROM suggested_solutions
  31. GROUP BY problem_id) ss
  32. ON problems.problem_id = ss.problem_id
  33. WHERE problem_categories.category_id = 1
  34. GROUP BY problem_title;
Advertisement
Add Comment
Please, Sign In to add comment