SQL Server sum from multiple tables in a single query

Question

here is the details

tbl1

rec_id,    rec_amount,       rec_date

1            1500             1/1/2011  
2            4500             1/1/2011  
3            500             1/1/2011  
4            15000             1/1/2011  
5            7500             1/1/2011

tbl2

vouc_id       vouc_amount        pay_date
1             15000              1/1/2011
2              750.50            1/1/2011
3              560                1/1/2011

tbl3

don_id      d_amount           d_date
1           1500                1/1/2011
2             2000              1/1/2011

I need sum(rec_amount) from tbl1 and sum(vouc_amount) from tbl2 and sum(d_amount) from tbl3 in a single query where date = 1/1/2011. any body can help me please?

what do you need, you need it in three different columns or in a single column with the total value? — Borja
– Borja, Commented Feb 24, 2011 at 15:23

SQLMenace · Accepted Answer · 2011-02-24 15:27:13Z

2

use union all between the tables and do a SUM on top of that

example

SELECT SUM(TheAmount)
FROM(
SELECT rec_amount AS TheAmount
FROM tbl1
WHERE  rec_date = '20110101'
UNION ALL
SELECT vouc_amount        
FROM tbl2
WHERE  pay_date = '20110101'
UNION ALL
SELECT d_amount           
FROM tbl3
WHERE  d_date = '20110101') x

or next to each other

SELECT SUM(rec_amount) AS tbl1Amount,(SELECT SUM(vouc_amount)   
FROM tbl2
WHERE  pay_date = '20110101') AS tbl2Amount ,(SELECT SUM(d_amount)          
FROM tbl3
WHERE  d_date = '20110101')  AS tbl3Amount  
FROM tbl1
WHERE  rec_date = '20110101'

edited Feb 24, 2011 at 15:27

answered Feb 24, 2011 at 15:21

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mikael Eriksson · Accepted Answer · 2011-02-24 15:27:40Z

You can do a sub select for each column.

declare @T1 table(rec_id int, rec_amount int, rec_date datetime)
declare @T2 table(vouc_id int, vouc_amount int, pay_date datetime)
declare @T3 table(don_id int, d_amount int, d_date datetime)

insert into @T1 values 
(1, 1500 , '2011-01-01'),
(2, 4500 , '2011-01-01'),
(3, 500  , '2011-01-01'),
(4, 15000, '2011-01-01'),
(5, 7500 , '2011-01-01')


insert into @T2 values
(1, 15000, '2011-01-01'),
(2, 750  , '2011-01-01'),
(3, 560  , '2011-01-01')

insert into @T3 values
(1, 1500, '2011-01-01'),
(2, 2000, '2011-01-01')


select
(select sum(rec_amount)
 from @T1
 where rec_date = '2011-01-01'),
(select sum(vouc_amount)
 from @T2
 where pay_date = '2011-01-01'),
(select sum(d_amount)
 from @T3
 where d_date = '2011-01-01')

Collectives™ on Stack Overflow

SQL Server sum from multiple tables in a single query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related