Sum two tables

Question

Post reply

Sum two tables

leesider

Old Hand

Points: 367
More actions
June 6, 2012 at 3:44 am

#275202

I have two tables with the same structure
Table One
year week total_savings
2012 10 72.2571543100001
2012 11 629.06429487
2012 12 1190.04593862
2012 13 1853.73274922
Table Two
year week total_savings
2012 13 104.24332919
2012 14 2165.64298191965
2012 15 11168.9722151692
I want to combine the two tables (summing the "total_savings" column) so that I have a new table with the same structure. Based on the table above I would need week 13's total_savings to be summed.
How can I do this? (I don't want to use a cursor)
Thanks for any replies.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

okbangas SSChampion Points: 11773 More actions · Answer 1

select t1.total_savings+t2.total_savings as total_savings

from

t1 inner join

t2 on t1.year = t2.year and t1.week = t2.week

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

leesider Old Hand Points: 367 More actions · Answer 2

okbangas (6/6/2012)
select t1.total_savings+t2.total_savings as total_savings from t1 inner join
t2 on t1.year = t2.year and t1.week = t2.week

My apologies, I didn't make myself clear. The above query goves me back

Yearweektotal_savings

2012131957.97607841

I need to get back

year week total_savings

2012 10 72.2571543100001

2012 11 629.06429487

2012 12 1190.04593862

2012 13 1957.97607841 (this is the combined total of week 13 from the two tables)

2012 14 2165.64298191965

2012 15 11168.9722151692

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

You need a full outer join.

declare @tab1 table (year int, week int, total_savings decimal(36,18))

declare @tab2 table (year int, week int, total_savings decimal(36,18))

insert into @tab1 (year, week, total_savings) values

(2012, 10, 72.2571543100001),

(2012, 11, 629.06429487),

(2012, 12, 1190.04593862),

(2012, 13, 1853.73274922)

insert into @tab2 (year, week, total_savings) values

(2012, 13, 104.24332919),

(2012, 14, 2165.64298191965),

(2012, 15, 11168.9722151692)

select

ISNULL(t1.year, t2.year) AS Year,

ISNULL(t1.week, t2.week) AS Week,

ISNULL(t1.total_savings,0) + ISNULL(t2.total_savings,0) AS TotalSavings

from

@tab1 t1

full outer join

@tab2 t2

on

t1.year = t2.year

and

t1.week = t2.week

leesider Old Hand Points: 367 More actions · Answer 4

anthony.green (6/6/2012)
You need a full outer join.
declare @tab1 table (year int, week int, total_savings decimal(36,18))
declare @tab2 table (year int, week int, total_savings decimal(36,18))
insert into @tab1 (year, week, total_savings) values
(2012, 10, 72.2571543100001),
(2012, 11, 629.06429487),
(2012, 12, 1190.04593862),
(2012, 13, 1853.73274922)
insert into @tab2 (year, week, total_savings) values
(2012, 13, 104.24332919),
(2012, 14, 2165.64298191965),
(2012, 15, 11168.9722151692)
select
ISNULL(t1.year, t2.year) AS Year,
ISNULL(t1.week, t2.week) AS Week,
ISNULL(t1.total_savings,0) + ISNULL(t2.total_savings,0) AS TotalSavings
from
@tab1 t1
full outer join
@tab2 t2
on
t1.year = t2.year
and
t1.week = t2.week

I think that did it, thanks man 🙂

vinu512 SSCoach Points: 15729 More actions · Answer 5

You can do it like this:

Select a.year, a.week, SUM(a.total_savings) From

(Select * From Ex

union

Select * From Ex1) As a

Group By a.year, a.week

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

okbangas SSChampion Points: 11773 More actions · Answer 6

vinu512 (6/6/2012)
You can do it like this:
Select a.year, a.week, SUM(a.total_savings) From
(Select * From Ex
union
Select * From Ex1) As a
Group By a.year, a.week

If so, you have to use union all, as total_savings may be equal in the two tables...

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

vinu512 SSCoach Points: 15729 More actions · Answer 7

okbangas (6/6/2012)
vinu512 (6/6/2012)
You can do it like this:
Select a.year, a.week, SUM(a.total_savings) From
(Select * From Ex
union
Select * From Ex1) As a
Group By a.year, a.week
If so, you have to use union all, as total_savings may be equal in the two tables...

Hmm, True. Thanks for pointing it out.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉