Counting these Rows, and, Perhaps, Giving Percentages
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.
you'd have to use some dynamic sql, building your query once you know how many columns it'll have, but sure.
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?
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.
This is a bit of psuedo code that should get you started.
Then you can do something like:
WHEN (entered value == current column index) THEN 1
END CASE as (columns named 0...n),
1 as nplus1
from your_table where poll_id = some_poll_id;
Should give you at a minimum where to start.
sum(column index 0...n) as sumcol0..n,
sum(nplus1) as sumnplus1
from the previous select statement;
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
WHERE poll_id = 1
GROUP BY answer_id ) a,
(SELECT count(answer_id) total_replies
WHERE poll_id = 1) b;
Last edited by pollymac; 01-22-2009 at 06:00 PM.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)