Counting these Rows, and, Perhaps, Giving Percentages

[evenstar7139]

I've got this table called poll_answers and the columns it has that pertains to this are row_id, poll_id, and answer_id. row_id is the primary key. Whenever somebody makes an answer to a poll, the poll_id and answer_id are inserted into the database. This is how I know which answers go with which poll.

Well, what I want this query to do is go search for the answers for a particular poll, for instance, a poll with a poll_id of 2, and figure out how many times each answer has been given and, if this is possible, what percentage of people gave that answer.

I don't know if this matters, but the number of possible answers a poll can have varies between two and ten.

[chazzy]

you'd have to use some dynamic sql, building your query once you know how many columns it'll have, but sure.

[evenstar7139]

I can have the PHP figure out how many columns there will be. How exactly do you need it to do it? Does it just need to pass on a number? Like 3 if there's three columns?

[chazzy]

I think, amongst other things, you want to use a series of case statements to build a set of n+1 columns, where n is the number of poll options, and the last column being the total number of votes.
http://dev.mysql.com/doc/refman/5.0/...statement.html

This is a bit of psuedo code that should get you started.

select
CASE
    WHEN (entered value == current column index)  THEN 1
    ELSE 0
END CASE as (columns named 0...n),
1 as nplus1
from your_table where poll_id = some_poll_id;

Then you can do something like:

select
sum(column index 0...n) as sumcol0..n,
sum(nplus1) as sumnplus1
from the previous select statement;

Should give you at a minimum where to start.

[pollymac]

SELECT answer_id, answer_total, round(answer_total/total_replies*100,2) answer_pct, total_replies
FROM (SELECT Count(answer_id) answer_total, answer_id
      FROM poll_answers
      WHERE poll_id = 1
      GROUP BY answer_id ) a, 
     (SELECT count(answer_id) total_replies
      FROM poll_answers
      WHERE poll_id = 1) b;

??