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

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
Thread
help with queryjabbott23 Oct
  • Re: help with querySGreen23 Oct
  • Re: help with queryPeter Brawley23 Oct