List:General Discussion« Previous MessageNext Message »
From:Scott Hamm Date:November 5 2004 3:04pm
Subject:RE: Column grouped by months
View as plain text  
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:Michael.Stassen@stripped]
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?
> 
Thread
Column grouped by monthsScott Hamm5 Nov
  • Re: Column grouped by monthsBrent Baisley5 Nov
  • Re: Column grouped by monthsSGreen5 Nov
    • Re: Column grouped by monthsSGreen5 Nov
  • Re: Column grouped by monthsMichael Stassen5 Nov
  • Re: Column grouped by monthsGleb Paharenko5 Nov
RE: Column grouped by monthsScott Hamm5 Nov
  • Re: Column grouped by monthsMichael Stassen5 Nov
    • Re: Column grouped by monthsMichael Stassen5 Nov
RE: Column grouped by monthsScott Hamm5 Nov