I FORGOT my GROUP BY!!!! (arrrrrrrggggghhhhh!) [EMAIL PROTECTED] wrote on 11/05/2004 09:28:20 AM:
> You were very close. What you are trying to produce is called a cross-tab > report or a pivot table (depending on who you ask) > > SELECT category.category, > sum(if(goaldata.goalmonth=11, reqvalue, 0)) as Nov, > sum(if(goaldata.goalmonth=12, reqvalue, 0)) as Dec > FROM category > INNER JOIN goaldata > ON goaldata.catid=goaldata.id; GROUP BY category.category > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > Scott Hamm <[EMAIL PROTECTED]> wrote on 11/05/2004 09:05:50 AM: > > > My current database: > > > > mysql> SELECT > > -> Category.Category, > > -> GoalData.Reqvalue, > > -> GoalData.GoalMonth > > -> FROM > > -> goaldata, > > -> category > > -> WHERE > > -> goaldata.catid=category.id; > > +-------------------------------------+----------+-----------+ > > | Category | Reqvalue | GoalMonth | > > +-------------------------------------+----------+-----------+ > > | Mailroom Mail Opening Orders | 54 | 11 | > > | Mailroom Rewards | 150 | 11 | > > | Mailroom QC Opening Orders | 135 | 12 | > > | Mailroom Opening Surveys | 200 | 11 | > > | Mailroom QC Surveys | 350 | 11 | > > | Mailroom Opening Resubmissions | 90 | 11 | > > | Mailroom QC Resubmissions | 250 | 12 | > > | Mailroom Microfilming | 700 | 11 | > > | Mailroom Taping | 175 | 11 | > > | Mailroom QC Taping | 350 | 11 | > > | Mailroom Scanning | 1200 | 12 | > > | Data Entry Orders (Key from paper) | 35 | 11 | > > | Date Entry Surveys (Key form paper) | 45 | 11 | > > | Data Entry Resubmissions | 50 | 11 | > > | Data Entry Orders (Key from image) | 30 | 12 | > > | Data Entry Surveys (Key from image) | 50 | 11 | > > ... > > > > > > I want my output to look like this: > > > > +-------------------------------------+----------+----------+ > > | category | Nov | Dec | > > +-------------------------------------+----------+----------+ > > | Mailroom Mail Opening Orders | 54 | 54 | > > | Mailroom Rewards | 150 | 150 | > > | Mailroom QC Opening Orders | 135 | 135 | > > | Mailroom Opening Surveys | 200 | 200 | > > | Mailroom QC Surveys | 350 | 350 | > > | Mailroom Opening Resubmissions | 90 | 90 | > > | Mailroom QC Resubmissions | 250 | 250 | > > | Mailroom Microfilming | 700 | 700 | > > | Mailroom Taping | 175 | 175 | > > | Mailroom QC Taping | 350 | 350 | > > | Mailroom Scanning | 1200 | 1200 | > > | Data Entry Orders (Key from paper) | 35 | 35 | > > | Date Entry Surveys (Key form paper) | 45 | 45 | > > | Data Entry Resubmissions | 50 | 50 | > > | Data Entry Orders (Key from image) | 30 | 30 | > > | Data Entry Surveys (Key from image) | 50 | 50 | > > | Data Entry QC | 55 | 55 | > > +-------------------------------------+----------+----------+ > > > > Something like this didn't work -- > > SELECT > > category.category, > > goaldata.reqvalue, > > goaldata.goalmonth=11 as Nov, > > goaldata.goalmonth=12 as Dec > > FROM > > category, > > goaldata > > WHERE > > goaldata.catid=goaldata.id; > > > > How can I get around to it? > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
