Totalling up ballot results

Question

I have a ballot where each voter gets 3 votes, choosing from 10 different candidates. Vote 1 is allocated 3 points, vote 2 gets 2 points and vote 3 gets 1 point.

I have the following SQL queries to total the number of points gained from each of the votes (so separate results for votes 1, 2 and 3).

What I need to do is put all these results together in a single table, but I'm not too sure where to start.

SELECT cn.cand_name, (count(vote_1) * 3) as vote_1 FROM candidate_votes cv Inner Join candidate_names cn ON cv.vote_1 = cn.cand_number GROUP BY cand_name;

SELECT cn.cand_name, (count(vote_2) * 2) as vote_2 FROM candidate_votes cv Inner Join candidate_names cn ON cv.vote_2 = cn.cand_number GROUP BY cand_name;

SELECT cn.cand_name, (count(vote_3) * 1) as vote_3 FROM candidate_votes cv Inner Join candidate_names cn ON cv.vote_3 = cn.cand_number GROUP BY cand_name;

I have the following results table:

Voter_number    Vote_1     Vote2      Vote3
123             cand_1     cand_3     cand_2
456             cand_2     cand_1     cand_3
789             cand_2     cand_3     cand_1

And the following candidate name table:

cand_number     cand_name
cand_1          Dave
cand_2          Sarah
cand_3          Nigel

So the results I'm looking for will look something like:

Candidate       Votes
Dave            6
Sarah           7
Nigel           5

Answer 1

SELECT
    cn.cand_name, 
    count(cv1.vote_1) * 3 as vote_1, 
    count(cv2.vote_2) * 2 as vote_2, 
    count(cv3.vote_3) as vote_3
FROM
    candidate_names cn
    LEFT JOIN
    candidate_votes cv1 ON cv1.vote_1 = cn.cand_number
    LEFT JOIN
    candidate_votes cv2 ON cv2.vote_2 = cn.cand_number
    LEFT JOIN
    candidate_votes cv3 ON cv3.vote_3 = cn.cand_number
GROUP BY cn.cand_name;

This also allows you to add all votes

(count(cv1.vote_1) * 3) +
    (count(cv2.vote_2) * 2) +
    count(cv3.vote_3) as totalvotes

Edit: rows are being multiplied by the JOIN which is why it's wrong for cand2 and cand3

SELECT
    cn.cand_name, 
    SUM(CASE WHEN cv.vote_1 = cn.cand_number THEN 3 ELSE 0 END) as vote_1, 
    SUM(CASE WHEN cv.vote_2 = cn.cand_number THEN 2 ELSE 0 END) as vote_2, 
    SUM(CASE WHEN cv.vote_3 = cn.cand_number THEN 1 ELSE 0 END) as vote_3
FROM
    candidate_names cn
    JOIN
    candidate_votes cv ON cn.cand_number IN (cv.vote_1, cv.vote_2, cv.vote_3)
GROUP BY cn.cand_name;