<[EMAIL PROTECTED]> 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.
>
> Advice?
>
> --ja
>
>
ja,
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:
SELECT questionID
, 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
FROM vote_table
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
columns).
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
