Rethabile Rethabile - 1 month ago 6
SQL Question

Trying to get count of votes in SQL based on ID

Table MAPPING:

--Solution_Votes
- ID int
- SolutionID string
- Vote int

--Solution
- ID int
- Solution
- VotesUp
- VotesDown

SELECT *,
(SELECT SUM(CASE WHEN voteUp = 1 THEN 1 ELSE 0 END) FROM Solutions_Votes) As VoteCountUp,
(SELECT SUM(CASE WHEN voteDown = 0 THEN 1 ELSE 0 END) FROM Solutions_Votes) As VoteCountDown
FROM Solution


When I run this query it gives me the count on each row for voteUpCount and voteDownCount. I need the count to be based on the solution ID so that each solution has its count of up votes and down votes. If anybody can help it would be appreciated. Thanks in advance!

Answer

Just use conditional aggregation. In your case this is simple:

select sv.solutionid,
       sum(case when sv.voteUp = 1 then 1 else 0 end) as VoteCountUp,
       sum(case when sv.voteDown = 0 then 1 else 0 end) as VoteCountDown
from solutions_votes sv
group by sv.solutionid;

You only need the solutions table if some solutions have no votes and you want to include them.

EDIT:

You would include solutions in various way. Here is one:

select s.*, ss.VoteCountUp, ss.VoteCountDown
from solutions s left join
     (select sv.solutionid,
             sum(case when sv.voteUp = 1 then 1 else 0 end) as VoteCountUp,
             sum(case when sv.voteDown = 0 then 1 else 0 end) as VoteCountDown
      from solutions_votes sv
      group by sv.solutionid
     ) ss
     on s.solutionid = ss.solutionid;