Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/forums/mysql-forum/)
-   -   Problem with SUM and GROUP (http://www.go4expert.com/forums/sum-t1385/)

SuNcO 13Sep2006 07:22

Problem with SUM and GROUP
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

pete_bisby 9Apr2009 14:39

Re: Problem with SUM and GROUP
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

All times are GMT +5.5. The time now is 21:03.