总计投票结果 [英] Totalling up ballot results

问题描述

我有一个投票，每个选民得到3票，从10个不同的候选人中选择。

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.

我有以下SQL查询来总计从每个点获得的点数（投票1,2和3的单独结果）。

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;

我有以下结果表：

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

和以下候选名称表：

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

推荐答案

SELECT cn.cand_name
     , COALESCE(cv1.cnt_1,0)
     , COALESCE(cv2.cnt_2,0)
     , COALESCE(cv3.cnt_3,0)
     , 3*COALESCE(cv1.cnt_1,0) + 2*COALESCE(cv2.cnt_2,0)
       + 1*COALESCE(cv3.cnt_3,0) AS total
FROM candidate_names AS cn
  LEFT JOIN 
    ( SELECT vote_1 AS vote
           , COUNT(*) AS cnt_1
      FROM candidate_votes cv
      GROUP BY vote_1
    ) AS cv1
    ON cv1.vote = cn.cand_number
  LEFT JOIN 
    ( SELECT vote_2 AS vote
           , COUNT(*) AS cnt_2
      FROM candidate_votes cv
      GROUP BY vote_2
    ) AS cv2
    ON cv2.vote = cn.cand_number
  LEFT JOIN 
    ( SELECT vote_3 AS vote
           , COUNT(*) AS cnt_2
      FROM candidate_votes cv
      GROUP BY vote_3
    ) AS cv3
    ON cv3.vote = cn.cand_number

这篇关于总计投票结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！