Sum two columns together

Question

I have two table which contain Year, Month, and Budget

I need to match both table by the year and month column and then sum both the budget figures…. How can I do this?

Below is the script to create the two tables.

select year, month, sum(budgetadj)

from #TableA

group by year, month

select year, month, sum([budget period])

from #TableB

group by year, month

#TableA

year                  month               budgetadj

2012/2013         May                  NULL

2012/2013         April                  NULL

2012/2013         July                   NULL

2012/2013         December         322396.826324463

2012/2013         September         NULL

2012/2013         October             NULL

2012/2013         June                  NULL

2012/2013         November         326287.964019775

2012/2013         August              NULL

#TableB

year                  month               budget period

2012/2013         June                  429536.83

2012/2013         September         494081.48

2012/2013         November         1045759.14

2012/2013         May                  488493.05

2012/2013         October             01784.91

2012/2013         December         799144

2012/2013         August              486133.56

2012/2013         April                  476041.56

2012/2013         July                   450107

I thought running the query below would get the answers, but it doesn’t!!

select a.year, b.month, sum(budgetadj)+sum([budget period])

from #tableA a

       left outer join #TableB b on a.year = b.year and a.month = b.month collate database_default

group by a.year, b.month

Answer 1

Are you looking for the below:

Drop table T1,T2
create Table T1(year Varchar(20),Month Varchar(20), Budget Decimal(18,8))
Insert into T1 Select '2012/2013','May',NULL
Insert into T1 Select '2012/2013','April',NULL
Insert into T1 Select '2012/2013','December',322396.826324463
create Table T2(year Varchar(20),Month Varchar(20), Budget Decimal(18,8))
Insert into T2 Select '2012/2013','May',234234
Insert into T2 Select '2012/2013','August',NULL
Insert into T2 Select '2012/2013','December',3223
Select year,Month,SUM(Budget) From
(
Select * From T1
Union All
Select * From T2 
)A
Group by YEAR,Month

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

Select year,Month,SUM(Budget) From
(
select year, month, budget
from #TableA
Union All
select year, month, budget
from #TableB
)A
Group by YEAR,Month

i get the following error message on column 2?

Msg 451, Level 16, State 1, Line 1

Cannot resolve collation conflict for column 2 in GROUP BY statement.

how do i resolve this?

Answer 3

Add default collation as you did....

collate database_default

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 4

Hi Sam,

Pls find the below code.

 

create table #TableA (year varchar(20), month varchar(20), budgetadj float)

Insert Into #TableA
 
SElect '2012/2013',         'May' ,                 NULL Union
 
SElect '2012/2013',        'April',                  NULL Union
 
SElect '2012/2013',       'July'  ,                 NULL Union
 
SElect '2012/2013',         'December',         322396.826324463 Union
 
SElect '2012/2013',        'September',         NULL Union
 
SElect '2012/2013',       'October'   ,          NULL Union
 
SElect '2012/2013',      'June'       ,           NULL Union
 
SElect '2012/2013',     'November'    ,     326287.964019775 Union
 
SElect '2012/2013',    'August'       ,       NULL

Select * From #TableA

 

Create Table #TableB (year Varchar(20), month Varchar(20),budgetperiod float)
 
Insert into #TableB
 
select '2012/2013',         'June',                  429536.83 union
 
select '2012/2013',         'September',         494081.48 union
 
select '2012/2013',         'November',         1045759.14 union
 
select '2012/2013',         'May' ,                 488493.05 union
 
select '2012/2013',         'October',             01784.91 union
 
select '2012/2013',         'December',         799144 union
 
select '2012/2013',         'August',              486133.56 union
 
select '2012/2013',         'April',                  476041.56 union
 
select '2012/2013',         'July' ,                 450107

SElect * From #TableB

Select *,isnull(A.budgetadj,0)+Isnull(b.budgetperiod,0) as sum From #TableA A left join #TableB B
On A.year = B.year And a.month = B.month

Drop Table #TableB

Drop Table #TableA

Thanks,

Nandhu