1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Problem with SUM and GROUP

Discussion in 'MySQL' started by SuNcO, Sep 13, 2006.

  1. SuNcO

    SuNcO New Member

    Sep 13, 2006
    Likes Received:
    Trophy Points:
    have a table with some like this fields :

    order, client_name, unit, type

    1 - nobody - 1 - orange
    2 - me - 2 - yellow
    3 - another - 1 - black
    1 - nobody - 1 - orange
    1 - nobody - 1 - orange

    I want to make a select that return GROUP by "type" field but at same
    time the SUM or "unit". Ok, works fine, but now i want to "order" must
    be UNIQUE.. i mean, the SUM in this case return 6 instead 4

    What can i do ? I use MySQL 4.1.18

    The select that i use in this case is :

    select type, sum(unit) from table group by type

    The expected result is :

    orange, 1
    yellow, 1
    black, 1
  2. pete_bisby

    pete_bisby New Member

    Nov 11, 2007
    Likes Received:
    Trophy Points:
    Software Developer/Analyst, PB
    Manchester, England
    Home Page:
    Your expected result doesn't match your example.

    If you want the information to return orange,1 yellow,2 black,1 then use SELECT DISTINCT:
    SELECT DISTINCT unit, type FROM table;

    However once you start including aggregate functions you will always have problems. Also, on a completely different point, don't use reserved words as field names (i.e. "order" and "type") - it isn't good practice and it'll cause problems later on.

    My advice - upgrade to MySQL 5 at least, then you can include sub-queries

Share This Page