problem with join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

problem with join

Author

Topic

Pethron
Starting Member

10 Posts

Posted - 2004-06-08 : 03:56:01

hi,
i have to tables t1 and t2.
the tables look like this:

t1
id1(int) wert1(int)
1	10
1	10
1	10
2	15
2	15
3	5
3	5
3	5
3	15
4	30 

t2
id2(int) wert2(int)
1	1
1	2
3	3
2	1
2	1
2	1
4	2
4	1

now i want to display this result:

id1	sum(wert1)	sum(wert2)
1	30		3
2	30		3
3	30		3
4	30		3

(this would take for each id the sum of wert1 and the sum of wert2)

i tried it with this command:


select
id1,
sum(wert1),
sum(wert2)
from t1
left join t2 on t2.id2 = t1.id1

group by 
id1

but the result is this:

id1	sum(wert1)	sum(wert2)
1	60		9
2	90		6
3	30		12
4	60		3

it takes for each value in t1 each value in t2 and so it sumize the whole columns.
i'm not sure, maybe it is simple. but now, i dont came to the sollution. please help my tired brain..

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-06-08 : 04:07:57

There was a similar question a few days ago, hope this helps:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35689

OS