SELECT DISTINCT sum(): return multiple rows

[bilibytes]

Hi,

i am having big headaches in trying to find out how to perform a very very simple query.

having a table like this one:

+--------------+----------+---------+
| name         | object1  | object2 |
+--------------+----------+---------+
| Mr Brown     |     1    |    1    |
| Mr Brown     |     1    |    0    |
| Mr Pullen    |     1    |    0    |
+--------------+----------+---------+

well i want to get this output:

| Mr Brown   |    2      |     1      |
| Mr Pullen  |    1      |      0     |

Which is the sum of the object1 and object2 column for each name

what I can get by now is:
SELECT DISTINCT name, object1, object2
which returns

| Mr Brown   |    1      |     1      |
| Mr Pullen  |    1      |      0     |
| Mr Brown   |    1      |      0     |

I deduce here that it returns the rows that have at least one column which is different from other rows.

Whenever i try to use sum() it will return 1 row like this:

| Mr Brown   |    3      |     1      |

Here the sum() does not distinguish name it will sum up all the rows together and return the name of the user that has the most approximate object1 and object2 values to the resulting row


If you have any suggestion please let me know

Thank you very much

Bilibytes

[r035198x]

[code=mysql]select distinct name, (select sum(object1) from tablename where name = t.name), (select sum(object2) from tablename where name = t.name) from tablename t[/code]

[bilibytes]

Thank you very much for taking the time, but it does not work.
it will return an error: "#1064 syntax error near ' ' on line 1 check the manual..."

I have tried a bunch of queries and they will return the sum of all records in that column without distinguishing the user_id.

If there is any other suggestion i'll be very greatfull


Thank you very much

biliBytes

[r035198x]

Post the exact query that you tried. Do you have a user_id in that table?

[bilibytes]

GOT THE SOLUTION!!

ok the trick was to use the GROUP BY clause,

SELECT name, SUM(object1) AS o1_sum, SUM(object2) AS o2_sum
FROM table GROUP BY name

The GROUP BY clause allows the sum() to group it's sums...
now it looks quite evident, but it wasn't! lol

Thank you for your help.

Bilibytes

[r035198x]

Better that you finally got it yourself. Good luck with the rest of it.