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]
