sum columns from 2 tables by date

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 columns from 2 tables by date

Author

Topic

kblurry
Starting Member

6 Posts

Posted - 2011-12-01 : 13:34:51

i have 2 tables:
table1 contains amount,date table2 contains amount, revenue,date

i need to:
sum table1.amount + table2.amount, sum table2.revenue
according to table1.date and table2.date

like: select sum(table1.amount + table2.amount), sum(table2.revenue)

from table1,table2
where table1.date and table2.date = '2008-02%'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-01 : 13:49:49

It can be done something like this:


SELECT
	COALESCE(t1.date,t2.date),
	ISNULL(SUM(t1.amount),0) + ISNULL(SUM(t2.amount),0),
	ISNULL(SUM(t2.revenue),0)
FROM
	table1 t1
	FULL JOIN table2 t2 ON t1.date = t2.date
WHERE
	COALESCE(t1.date,t2.date) >= '20080201' AND COALESCE(t1.date,t2.date) < '20080301'
GROUP BY
	COALESCE(t1.date,t2.date)

All those full joins and coalesce functions are to account for cases where you may have data only in one table or the other for some of the days. If that is not the case, you can throw out the coalesce/isnulls and use an inner join.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources