Problem with SUM and GROUP

SuNcO's Avatar
Newbie Member
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
0
pete_bisby's Avatar, Join Date: Nov 2007
Go4Expert Member
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