Re: Column grouped by months

[Brent Baisley]

You're kind of mixing display formatting with data retrieval. MySQL is 
a database, so it's display options for data are fairly limited, that's 
usually the job of the front end. But, if you still want to push it to 
format the way you requested, you need to do a join. Essentially, 
you're going select one months records and join it with another months 
records.

SELECT
        category.category,
        goaldata.reqvalue,
        Nov.goalmonth as Nov,
        Dec.goalmonth as Dec
FROM
        category
        LEFT JOIN goaldata as Nov ON category.id=Nov.catid AND Nov.goalmonth=11
        LEFT JOIN goaldata as Dec ON category.id=Dec.catid AND Dec.goalmonth=12
As you can see, scaling this up to 12 months can get pretty long and 
may take a while depending on the size of your data. Normally, you 
wouldn't haven't any joins, but would sort by month. Your front end 
would then reformat the data from a vertical orientation to a 
horizontal, which would be quicker and scale better.

On Nov 5, 2004, at 9:05 AM, 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]


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]