Rethabile - 4 months ago 22

SQL Question

`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;
```