Sum from 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

SQL Server 2005 Forums

Transact-SQL (2005)

Sum from tables

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-18 : 09:34:36

Hello,

I have three individual tables which are similar.
I have the quiries.

select sum(money) from a left join b
on(a.id=b.id)
where(b.case='given_value')

select sum(money) from c left join b
on(c.id=b.id)
where(b.case='given_value')

select sum(money) from d left join b
on(d.id=b.id)
where(b.case='given_value')

To get the total money, I have to get the sum of the results from
above quires.

Can I merge them into one query?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-18 : 09:41:09

[code]
select sum(money)
from
(
select money = sum(money) from a left join b
on(a.id=b.id)
where(b.case='given_value')
union all
select money = sum(money) from c left join b
on(c.id=b.id)
where(b.case='given_value')
union all
select money = sum(money) from d left join b
on(d.id=b.id)
where(b.case='given_value')
) m
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 09:41:28

may be this:-

select sum(money)
from
(select a.id,a.money
from a
union all
select c.id,c.money
from c
union all
select d.id,d.money
from d)t
left join b
on b.id=t.id
where(b.case='given_value')

if this is not what you desire,please provide some sample data from tables and expected result. also specify how tables are related to b (1 to 1 or 1 to many)

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-18 : 09:56:29

Sorry,
The queries are:

select sum(money) from a left join x
on(a.id=x.id)
where(x.case='22222')

select sum(money) from a left join y
on(a.id=y.id)
where(y.case='22222')

select sum(money) from a left join z
on(a.id=z.id)
where(z.case='22222')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 10:03:12

[code]select sum(money) from a
left join
(
select id,money from x
where(x.case='22222')
union all
select id,money from y
where(y.case='22222')
union all
select id,money from z
where(z.case='22222')
)t
on t.id=a.id[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-18 : 10:07:51

quote:
Originally posted by zhshqzyc

Sorry,
The queries are:

select sum(money) from a left join x
on(a.id=x.id)
where(x.case='22222')

select sum(money) from a left join y
on(a.id=y.id)
where(y.case='22222')

select sum(money) from a left join z
on(a.id=z.id)
where(z.case='22222')

why are you using LEFT JOIN here ?

KH
[spoiler]Time is always against us[/spoiler]

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-18 : 10:08:17

select id,money from x
where(x.case='22222')

But table x does not have money field.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 10:10:59

quote:
Originally posted by zhshqzyc

select id,money from x
where(x.case='22222')

But table x does not have money field.

wat about this?

select sum(money) from a 
left join 
(
select id from x
where(x.case='22222')
union all
select id from y
where(y.case='22222')
union all
select id  from z
where(z.case='22222')
)t
on t.id=a.id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources