none
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 Sam233 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 Sam233 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 Sam233 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 Sam233 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