Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a poll database as

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

How can I run a query to sum the votes for each answer to get results for the list of answers in the poll?

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

answer_id  |  answer  |  SUM of Votes  |  % of votes
share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

Assuming redundant poll_result.poll_id added intentionally to avoid extra join, you can get desired output for a given pool(parameter ':value') with

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  
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.