Page 1 of 1

count() votes from poll_votes table for all polls?

Posted: Thu Dec 10, 2009 9:31 pm
by JAB Creations
Here is my poll_votes table; each vote has it's own row...
Image

What I'm trying to do is count all the votes for all the options for all the polls; I'll reuse the same single SELECT query to display polls 1-10 on the control panel's poll management index page (where you can view all polls and if they are active their current results/counts).

I can do it individually per poll...

Code: Select all

SELECT id_poll, id_choice, count(id_choice) FROM poll_votes WHERE id_poll='3' GROUP BY id_choice
Image

However when I remove the WHERE and modify the query a bit...

Code: Select all

SELECT id_poll, id_choice, count( id_choice ) FROM poll_votes GROUP BY id_poll
...it only counts the number of poll options voted upon, not the count per option per poll.

In regards to visual application, I'm attempting to get every option's vote count for every poll instead of using the first query above for each poll. This is only for my poll management page where the polls are displayed as an index (of polls, say poll 1-10, 11-20, etc).

Visually I'm trying to have MySQL return the following array...

id_poll / id_choice / id_choice_count (count(id_choice))
3 / 1 / 36
3 / 2 / 55
3 / 3 / 15
3 / 4 / 134
4 / 1 / 43
4 / 2 / 11
4 / 3 / 49
5 / 1 / 17
5 / 2 / 18

With a result like that I could put a while statement and reset the pointer reusing the same query for the vote count (as I want to visually show the vote count on active polls).

I've seen no poll examples with a two table setup and queries that make sense and minimize queries in general. Nothing in any of the MySQL tutorials has struck me as having the ability to do this and all posts I've come across in general always veer away when approaching this question. Am I over-complicating this or I need to learn a new trick?

Re: count() votes from poll_votes table for all polls?

Posted: Fri Dec 11, 2009 8:23 am
by VladSun
I'm not sure what you need, but maybe you need to group by two fields - id_choice and id_pool - try it.