List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 22 2005 10:52pm
Subject:Re: help with query
View as plain text  
<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.
> Advice?
> --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 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
help with queryjabbott23 Oct
  • Re: help with querySGreen23 Oct
  • Re: help with queryPeter Brawley23 Oct