Michael and Shawn's suggestions came into exactly same error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Dec,
I'm running 5.0.1-alpha-nt. -----Original Message----- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Friday, November 05, 2004 9:42 AM To: Scott Hamm Cc: 'Mysql ' (E-mail) Subject: Re: Column grouped by months I believe you need a self join. Something like SELECT c.Category, g11.Reqvalue AS Nov, g12.Reqvalue AS Dec FROM goaldata g11 JOIN goaldata g12 ON g11.catid = g12.catid AND g11.GoalMonth = 11 AND g12.GoalMonth = 12 JOIN category c ON g11.catid=c.id; This might also work: SELECT c.Category, SUM(IF(g.GoalMonth = 11, g.Reqvalue, 0)) AS Nov, SUM(IF(g.GoalMonth = 12, g.Reqvalue, 0)) AS Dec, FROM goaldata g JOIN category c ON g.catid=c.id; GROUP BY c.Category; Note that if you ever have more than one row with a particular Category-GoalMonth combination (in the 2nd year, perhaps), the first query will give extra rows while the second will add the values. Assuming that's not what you want, you'd have to add an appropriate WHERE condition to limit the results to the right rows (months), or otherwise alter the query to fit however you decide to handle that case. Michael Scott Hamm wrote: > 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]
