sum from multiple tables - 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

General SQL Server Forums

New to SQL Server Programming

sum from multiple tables

Author

Topic

MJ_
Starting Member

2 Posts

Posted - 2009-07-15 : 10:15:59

If I have (example) two tables who look like the following (including data):

TABLE1 (3 rows)
id: 1
name: p
amount: 5
___________
id: 2
name: p
amount: 6
___________
id: 3
name: w
amount: 3

TABLE2 (4 rows)
id: 1
name: g
amount: 3
___________
id: 2
name: p
amount: 3
___________
id: 3
name: r
amount: 6
___________
id: 4
name: w
amount: 7

I need ONE query to sum all table1 and table2 values of `amount` under the following conditions: table1.name IN ('p','w') and table1.name = table2.name

I tried a query like SELECT SUM(TABLE1.amount) + SUM(TABLE2.amount) FROM TABLE1, TABLE2 WHERE TABLE1.name IN ('p','w') AND TABLE1.name = TABLE2.name

But the summation is not performed in the way I want it. Here it takes i.e. 4 times the value in a TABLE2 record where this TABLE2 row matches 4 TABLE1 rows. I hope you understand, it's quite difficult for me to explain.

If it would work correctly the query result would be 24 with the data above (5 + 6 + 3 for all 'p' + 3 + 7 for all 'w').

Lots of thanks to anyone who tries to help me out here.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-15 : 10:41:14

select SUM(t1.amount + t2.amount) as amount
from TABLE1 as t1
left join TABLE2 as t2
on t1.name = t2.name
where t1.name IN ('p','w')

MJ_
Starting Member

2 Posts

Posted - 2009-07-15 : 10:54:57

I still have the problem which I tried to describe in the post. It double sums the id 2 'p' row from table2 because there are 2 'p' records in table1.

It does the following: (5 + 3) + (6 + 3) + (3 + 7)

but it should be: (5 + 6 + 3) + (3 + 7) instead...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-15 : 12:58:28

[code]
SELECT SUM(t1.amt1 + t2.amt2) as total
FROM (SELECT name,SUM(amount) AS amt1
FROM table1
WHERE name IN ('p','w')
GROUP BY name)t1
INNER JOIN (SELECT name,SUM(amount) AS amt2
FROM table2
WHERE name IN ('p','w')
GROUP BY name)t2
ON t2.name=t1.name
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources