<jabbott@stripped> wrote on 10/22/2005 06:24:07 PM:
> I have a table of votes with four fields, a primary key, and userID,
> that are just there for tracking purposes. But then I have
> questionID and vote fields. For each question, a user could pick a
> number of it's importance from 1 to 5. That number is what gets
> stored in the vote field.
> So, now I want to tabulate the votes. I thought I could just do a
> count with a group by but that isn't working. What I want to do is
> for each question, get a count of how many ones, how many twos, how
> many threes, etc.
What you are looking for is called a "crosstab query" or a "pivot table"
(lot's of literature out there on both of these subjects). Here is a
general pattern you should be able to easily adapt for your needs:
, SUM(if(importance=1,1,0)) as votes_for_1
, SUM(if(importance=2,1,0)) as votes_for_2
, SUM(if(importance=3,1,0)) as votes_for_3
, SUM(if(importance=4,1,0)) as votes_for_4
, SUM(if(importance=5,1,0)) as votes_for_5
GROUP BY questionID;
Alternative paterns can use the COUNT() aggregate function (depending on
your data and the choices you want to make about dividing your data into
Unimin Corporation - Spruce Pine