I can't seem to get to the online manual to check at the moment, but I'd
guess that dec is a reserved word. Sorry about that. Just put single
quotes around it.
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;
Or
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;
Michael
Scott Hamm wrote:
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]
