How to use SUM and GROUP together?

Discussion in 'MySQL' started by aayybb, Sep 28, 2009.

0
  1. #1
    Hi,

    I have a table like below. 3 columns and 10 rows.
    I would like to have result of the id ordered by ordered total.
    I used "select id, sum(ordered) as total group by parent_id order by total".
    I am hoping to get a7 12, a4 6, a1 5. But I am not. What did I do wrong?

    id ordered parent_id
    a1
    a2 3 a1
    a3 2 a1
    a4
    a5 1 a4
    a6 5 a4
    a7
    a8 4 a7
    a9 0 a7
    a10 8 a7
     
    aayybb, Sep 28, 2009 IP
  2. plog

    plog Peon

    Messages:
    298
    Likes Received:
    11
    Best Answers:
    1
    Trophy Points:
    0
    #2
    SELECT parent_id, SUM(ordered) as total FROM tablename group by parent_id order by total;

    In the select portion of your query you are using the wrong id field. Select the 'parent_id' field instead of the 'id' field.
     
    plog, Sep 28, 2009 IP
  3. aayybb

    aayybb Peon

    Messages:
    128
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Hi plog,

    Actually I did have parent_id. Just forgot to include it here. Sorry. The program I have is a much complicated one and I don't want to bother people here with a long code.I will keep looking. Thanks for the help.
     
    aayybb, Sep 28, 2009 IP
  4. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #4
    This should work fine. Don't know your table name, but...

    SELECT id, SUM(ordered) AS total FROM my_table GROUP BY parent_id, ORDER BY total DESC;
     
    jestep, Sep 28, 2009 IP
  5. aayybb

    aayybb Peon

    Messages:
    128
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Question. The query will also try to group the rows with parent_id equal to null/blank (the rows a1, a4, a7 whose parent_id is null or 0) What should I do though? Thanks for any help in advance.
     
    aayybb, Sep 29, 2009 IP
  6. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #6
    Add a Where parent_id IS NOT NULL and it should clear it up. If it's possibly for parent_id to end up being 0 or '', you may want to account for that as well.

    SELECT
    id,
    SUM(ordered) AS total
    FROM my_table
    WHERE (parent_id IS NOT NULL AND parent_id != '')
    GROUP BY parent_id,
    ORDER BY total DESC;
     
    jestep, Sep 29, 2009 IP
  7. aayybb

    aayybb Peon

    Messages:
    128
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #7
    Thank you very much. The original DB has extra columns, like color, age...
    I also need to add "where color = 'red' and color = 'green' " kind of condition to the query . Every thing should works but the original query I was working on before adding sum and group by has several left join and it is kind of confusing to me. I need to digest it slowly.
    Can someone give me an example of a left join embedded inside another left join with "sum", "group by", "where"? What should produce after the embedded left join, sum, group by, where....? Thanks for any help in advance.
     
    aayybb, Sep 30, 2009 IP