tobeyt23 Posted August 20, 2007 Share Posted August 20, 2007 I have created a table to track poll results, i would like to pull number of total votes, number of votes per answers id then get avg of votes. Used this sql: SELECT p.question, a.answer, count( r.polls_id ) AS total, count( r.poll_answers_id ) AS answer_total FROM polls p, poll_answers a, poll_results r WHERE p.polls_id = a.polls_id AND p.polls_id = r.polls_id AND a.poll_answers_id = r.poll_answers_id AND p.polls_id =2 GROUP BY p.polls_id, r.poll_answers_id Get these results: Do you think the U.S. should or should not have a ... Should Have 3 3 Do you think the U.S. should or should not have a ... Should Not Have 1 1 Obviously the numbers are not correct as i am getting both for total votes and total answers the same. How should this SQL be formated or do I need to do separate queries Here are the table structures: polls: polls_id int(10) unsigned PRI NULL auto_increment question text active tinyint(4) 0 poll_answers: poll_answers_id int(10) unsigned PRI NULL auto_increment answer varchar(250) polls_id int(11) 0 poll_results: poll_results_id int(10) unsigned PRI NULL auto_increment poll_answers_id int(11) 0 polls_id int(11) 0 user_ip varchar(32) Thanks in advance for any suggestive help! Quote Link to comment Share on other sites More sharing options...
tobeyt23 Posted August 21, 2007 Author Share Posted August 21, 2007 ended up create another query to pull total, still wondering if this could be done differently? Quote Link to comment Share on other sites More sharing options...
bache Posted August 21, 2007 Share Posted August 21, 2007 you can do it with one query but your row will have the following structure for example: answer | votes per this answer | total votes for the poll answer | votes per this answer | total votes for the poll answer | votes per this answer | total votes for the poll i mean that it will repeat "total votes for the poll" for every answer from the same question Quote Link to comment Share on other sites More sharing options...
tobeyt23 Posted August 21, 2007 Author Share Posted August 21, 2007 That is fine if total votes are the same but i need votes per answer to be different and when added up eqaul total votes ... currently not getting that Quote Link to comment Share on other sites More sharing options...
tobeyt23 Posted August 22, 2007 Author Share Posted August 22, 2007 Still looking for a better way to handle this, anything? Quote Link to comment Share on other sites More sharing options...
$username Posted August 22, 2007 Share Posted August 22, 2007 Are you doing this with in PHP? Brett Quote Link to comment Share on other sites More sharing options...
tobeyt23 Posted August 22, 2007 Author Share Posted August 22, 2007 Yes Quote Link to comment Share on other sites More sharing options...
$username Posted August 22, 2007 Share Posted August 22, 2007 Can you post the code that is doing the call? Brett Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.