如何“求和”和“加入”單次查詢投票結果？

我有一個民意調查數據庫

CREATE TABLE poll_answer
answer_id,
answer varchar(255),
poll_id int(11),
FOREIGN KEY(poll_id) REFERENCES polls(poll_id) ON DELETE CASCADE,
PRIMARY KEY(answer_id)
) ENGINE=InnoDB

CREATE TABLE poll_results
vote_id int(11),
user_id int(11),
answer_id int(11),
poll_id int(11),
FOREIGN KEY(answer_id) REFERENCES poll_answers(answer_id) ON DELETE CASCADE,
FOREIGN KEY(poll_id) REFERENCES polls(poll_id) ON DELETE CASCADE,
PRIMARY KEY(vote_id)
) ENGINE=InnoDB

如何執行查詢來匯總每個答案的投票，以獲得投票中答案列表的結果？

SELECT ... FROM poll_results ... WHERE poll_results.poll_id='x'

answer_id  |  answer  |  SUM of Votes  |  % of votes

假設poll_result.poll_id有意添加冗餘以避免額外連接，您可以獲得給定池（參數’：值’）的所需輸出

SELECT a.answer_id, a.answer, COUNT(1)  as num_votes,
IF(b.total = 0,0,100*COUNT(1)/b.total ) as pcnt
FROM poll_answer a 
LEFT JOIN poll_results b ON (b.answer_id = a.answer_id)
INNER JOIN (select count(1) as total from poll_results WHERE poll_id = :value)b on (1=1)
WHERE a.poll_id = :value
GROUP BY a.answer_id, a.answer  
-- I know mysql is ok with just GROUP BY a.answer_id, but just to make it more standard,
-- I use GROUP BY a.answer_id, a.answer  

引用自：https://dba.stackexchange.com/questions/43646