tricky query question: group into new table rows?

Discussion in 'Databases' started by falcondriver, Jul 8, 2007.

0
  1. #1
    hiya,

    i have a table "orders" with a product_id (1-5), date_format(timestamp), sum() of all sales plus the product name (via join).
    query works fine,and i get something out like:

    month, product_id, sales
    5/2007 - 1 - 123
    5/2007 - 2 - 456
    5/2007 - 3 - 789
    5/2007 - 4 - 12
    5/2007 - 5 - 345

    4/2007 - 1 - 123
    4/2007 - 2 - 456
    4/2007 - 3 - 789
    4/2007 - 4 - 12
    4/2007 - 5 - 345
    etc.

    now for my cool php/flash chart application i need each product_id as its own row, something like:

    month, product_1, product_2, product_3, product_4, product_5
    5/2007 - 123 - 456 - 789 -12 - 345
    4/2007 - 123 - 456 - 789 -12 - 345

    can someone please point me into the right direction here? whats the command to get this row/group thing done?
     
    falcondriver, Jul 8, 2007 IP
  2. falcondriver

    falcondriver Well-Known Member

    Messages:
    963
    Likes Received:
    47
    Best Answers:
    0
    Trophy Points:
    145
    #2
    i did it:

    SELECT
    DATE_FORMAT(`stamp`,"%M %Y") as `Month`,
    SUM(IF(`product_id`=1, `price`,0)) as `Product 1`,
    SUM(IF(`product_id`=2, `price`,0)) as `Product 2`,
    SUM(IF(`product_id`=3, `price`,0)) as `Product 3`,
    SUM(IF(`product_id`=4, `price`,0)) as `Product 4`,
    SUM(IF(`product_id`=5, `price`,0)) as `Product 5`
    FROM `order_items`
    GROUP BY `Month`
    ORDER BY `stamp` DESC
    LIMIT 0, 24

    pretty simple if you know how :)
     
    falcondriver, Jul 8, 2007 IP