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