locked
sum inside case RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi, 

    Can we  add a sum inside a case statment??

    i was getting an error ..

     

    select  RIGHT('00000'+ CONVERT(VARCHAR,b.building_number),5)as building_number, b.Building_name, 
    COUNT(*)NutrikidsEnrollment

        ,negativebalance = case 
                            when balance <0 and s.activeness =1 then SUM(balance)else 0 end  --getting an error here
        from students s inner join Buildings b on
     s.Building_ID = b.Building_ID 
     where  s.Activeness =1
      group by Building_Name,building_number
    order by Building_Number

    Thanks

    Thursday, May 28, 2015 3:52 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Case statement is executed per row and here you are doing a group by on Buliding_Name, buliding_number to which you want to aggregate the balance based on a condition then you have to put the sum outside the Case statement:

    sum(case when balance <0 and s.activeness =1 then balance else 0 end)

    or add that balance field also in the Group by clause.

    • Proposed as answer by Eric__Zhang Friday, May 29, 2015 6:06 AM
    • Marked as answer by Eric__Zhang Monday, June 8, 2015 3:20 AM
    Thursday, May 28, 2015 4:12 PM