simple query?

[mounte]

Hi, I've got two tables like this:
name-table
uid | name | surname

and
data-table
cid | uid | type | data

where type can be an integer from 1 to 10
the uid-fields is the relation.

I would like to do something like:
SELECT Name.uid, Name.name, SUM(Data.data) AS data FROM Name INNER JOIN Data ON Name.uid = Data.uid WHERE (Counters.type = @type) GROUP BY Name.name, Name.uid

except that I would like to get rid of doing this query for every @type
so what I want is the following result:

name | surname | type | Sum(data) where type and uid is unique

so with this data:
0, test0, test00
1, test1, test11

0, 0, 1, 5
1, 0, 1, 10
2, 0, 2, 3
3, 1, 4, 3
4, 1, 7, 2
5, 1, 7, 8

I want the following table:
0, test0, test00, 1, 15
0, test0, test00, 2, 3
1, test1, test11, 4, 3
1, test1, test11, 7, 10


any suggestions?
I can restructure the tables if it would make things easier.

[skhanal]

You can include type in group by

SELECT Name.uid, Name.name, type,SUM(Data.data) AS data FROM Name INNER JOIN Data ON Name.uid = Data.uid GROUP BY Name.name, Name.uid,type

[mounte]

Thanks for the help, but still ... it does not work. The summation is wrong.

I have rethinked some stuff and maybe the best result for me would be:


name1, surname1, sum(data where type = 1 and name = name1), sum(data where type = 2 and name = name1), ... , sum(data where type = 10 and name = name2)

name2, surname1, sum(data where type = 1 and name=name2), sum(data where type = 2 and name=name2), ... , sum(data where type = 10 and name = name2)

[skhanal]

I missed surname in my previous post

SELECT Name.uid, Name.name, Name.surname, type,SUM(Data.data) AS data FROM Name INNER JOIN Data ON Name.uid = Data.uid GROUP BY Name.name, Name.name, Name.uid,type