1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Problem with SUM and GROUP

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

  1. SuNcO

    SuNcO New Member

    Joined:
    Sep 13, 2006
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    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

    Joined:
    Nov 11, 2007
    Messages:
    48
    Likes Received:
    2
    Trophy Points:
    0
    Occupation:
    Software Developer/Analyst, PB
    Location:
    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