# 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

Wednesday, February 13, 2013 8:28 AM

### Answers

• 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.

• Marked as answer by Wednesday, February 13, 2013 9:16 AM
Wednesday, February 13, 2013 8:38 AM
• 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.

• Marked as answer by Wednesday, February 13, 2013 9:23 AM
Wednesday, February 13, 2013 9:05 AM

### All replies

• 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.

• Marked as answer by Wednesday, February 13, 2013 9:16 AM
Wednesday, February 13, 2013 8:38 AM
• ```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?

Wednesday, February 13, 2013 9:01 AM
• 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.

• Marked as answer by Wednesday, February 13, 2013 9:23 AM
Wednesday, February 13, 2013 9:05 AM
• 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

Wednesday, February 13, 2013 9:35 AM