www.webdeveloper.com
Results 1 to 5 of 5

Thread: Counting these Rows, and, Perhaps, Giving Percentages

  1. #1
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357

    Question 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.

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    you'd have to use some dynamic sql, building your query once you know how many columns it'll have, but sure.

  3. #3
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    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?

  4. #4
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    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.
    Code:
    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:

    Code:
    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.

  5. #5
    Join Date
    Jul 2008
    Location
    Carnoustie, Scotland
    Posts
    36
    Code:
    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;
    ??
    Last edited by pollymac; 01-22-2009 at 07:00 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles