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
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
