
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
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_pollIn 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?