You must Sign In to post a response.
  • Category: ASP.NET

    How to sum two columns in sql?

    Hi All,

    Name Mon Year Pay Balance Total PhoneNo
    ---------------------------------------------------------
    priya mar 2012 200 0 44546
    Punitha mar 2012 200 -100 45365
    fsd mar 2012 200 -100 2341
    Mani mar 2012 200 0 2313123
    gsgs mar 2012 200 0 f2123`
    fdgfsdg mar 2012 200 0 48582374
    yuvaraj mar 2012 300 -100 4578648


    in that table i need to calculate total for each row of PAy and BAlance column and should display like...
    \Name Mon Year Pay Balance Total PhoneNo
    ---------------------------------------------------------
    priya mar 2012 200 0 -200 44546
    Punitha mar 2012 200 -100 -300 45365
    fsd mar 2012 200 -100 -300 2341
    Mani mar 2012 200 0 -200 2313123
    gsgs mar 2012 200 0 -200 f2123`
    fdgfsdg mar 2012 200 0 -200 48582374
    yuvaraj mar 2012 300 -100 -300 4578648



    My query is
    declare @master table(CustomerName nvarchar(100),Phone nvarchar(20),ud int, Mothly_rental nvarchar(max), Balance int,Tot_TobePaid nvarchar(max))
    declare @user table(Bal int,userid int)
    insert into @master
    select Uname as CustomerName,phone,uid ,Mothly_rental, '' as balance ,'' as Tot_TobePaid from user_master where uid not in(select uid from user_details where month_details='mar' and year_details=2012 )
    --select * from @master
    insert into @user
    select Total_bal,uid from user_details where bal_amt>0-- and Month_Details='feb' and year_details=2012 --where uid in(select ud from @master) and is_current=1
    update @master set Balance= Bal from @master,@user
    where ud =userid
    --select * from @master
    select CustomerName,'mar' as MonthDetails,2012 as YearDetails,Mothly_Rental as TobePaid,Balance,
    (select sum (Balance-Mothly_rental) from @master group by Balance, mothly_rental)as Tot_Tobepaid from @master

    select CustomerName,'mar' as MonthDetails,2012 as YearDetails,Mothly_Rental as TobePaid,Balance,
    Tot_TobePaid , Phone from @master






    But it showing error lik

    Msg 512, Level 16, State 1, Line 12
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


    Pls tel me the right solution
  • #651451
    select CustomerName,'mar' as MonthDetails,2012 as YearDetails,Mothly_Rental as TobePaid,Balance,
    (select sum (Balance-Mothly_rental) from @master group by Balance, mothly_rental)as Tot_Tobepaid from @master


    This Query may return more result hence you are getting this error

    Please mark this as Answer, if this helps

    Regards,
    Alwyn Duraisingh.M 
    << Database Administrator >>
    Jesus saves! The rest of us better make backups...

  • #651452
    If i understand correctly then your last row is wrong
    yuvaraj mar 2012 300 -100 -300 4578648
    it should be
    yuvaraj mar 2012 300 -100 -400 4578648

    and you can get the output using below formula
    (Payment*-1)+balance=total

    correct me if i misunderstood the requirement

  • #651453
    yes alwyn.. Pls tel me the alternative solution

  • #651460
    Hi,



    select CustomerName,'mar' as MonthDetails,2012 as YearDetails,Mothly_Rental as TobePaid,Balance,
    (select sum (Balance-Mothly_rental) from @master group by Balance, mothly_rental)as Tot_Tobepaid from @master



    In the above query mentioned by Alwyn . you can use "WHERE " condition in inner select statement or use "Limit" to get single record in Inner Select.

    With Regards
    Kushal

  • #651463
    Hi pls modify that query and tel me ..

  • #651465
    select CustomerName,'mar' as MonthDetails,2012 as YearDetails,Mothly_Rental as TobePaid,Balance,
    (select sum (Balance-Mothly_rental) from @master group by Balance, mothly_rental)as Tot_Tobepaid from @master

    there is no mapping between
    (select sum (Balance-Mothly_rental) from @master group by Balance, mothly_rental)

    and Main Query table @master

    Try something like this

    select CustomerName,'mar' as MonthDetails,2012 as YearDetails,@master.Mothly_Rental as TobePaid,@masterBalance,Tot_Table.Tot_TobePaid
    from @master ,
    (select sum (Balance-Mothly_rental) Tot_TobePaid,Balance, mothly_rental from @master group by Balance, mothly_rental)as Tot_Table
    where Tot_Table.Balance=@master.Balance and Tot_Table.mothly_rental=@master.mothly_rental

    make it as two tables and map them to get total for respective Balance and Monthly rental

  • #651472
    thank u all.. i declare @total and i updated sum value in @ master.. now i get worked with that...


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.