Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Posts
    6

    Unanswered: Need Help with query.

    Using: Mysql 4.0
    MySqlFront 2.5

    I have a table with ifnromation like so:

    item sales date
    ROM 2004-04-30
    INC 2004-03-02

    How can I write a query so that the information is grouped and displayed horizontally?

    ie:
    Itm Jan Feb MAr Apr
    ROM 0 0 0 1
    INC 0 0 1 0

    If anyone can help, it would be grteatly appreciated.

  2. #2
    Join Date
    May 2004
    Posts
    6

    Been reading a couple of other posts and thought I should add more info.

    First off this isn't a homework assignment.

    I just can't figure out how to make case statements or something along those lines in order to group results into cloumns.

    If anyone could simply point me in the right direction, that'll be all I need.


    Thanks.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use a Sum() function with a CASE statement to make the pivot work. Sum up the rows that are in the right month.

    -PatP

  4. #4
    Join Date
    May 2004
    Posts
    6

    thanks Pat

    I will try that, and hopefully post the working code for others to learn from.

  5. #5
    Join Date
    May 2004
    Posts
    6

    Thanks Pat, your tip worked!

    Here is the code in order to use Sum and case to group results.

    SELECT SUM (CASE when sales_date >= '2003-01-01' and
    sales_date <= '2003-01-31' then qty else 0 end)
    as 'Jan',
    SUM (CASE when sales_date >= '2003-02-01' and
    sales_date <= '2003-02-28' then qty else 0 end)
    as 'Feb',
    FROM sales

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just to avoid loosing lost hair (from pulling it out), I'd tweak your syntax just a smidgeon.
    Code:
    SELECT SUM (CASE when sales_date >= '2003-01-01'
          and sales_date < '2003-02-01' then qty end) as 'Jan'
    ,  SUM (CASE when sales_date >= '2003-02-01'
          and sales_date <= '2003-03-01' then qty end) as 'Feb'
       FROM sales
    This gets you around needing to figure out how many days are in which months (which is just lazy on my part), but it also prevents you from loosing any data entered with a time on the last day of the month too!

    -PatP

  7. #7
    Join Date
    May 2004
    Posts
    6

    Thanks Pat.

    You're right it makes more sense your way.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •