Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    10

    Unanswered: Help with using Case statement in a calculation

    I am having trouble using the case statement in a calculation. I want to get the sum amount if the sum of pct >1 using pct_cur if pct_cur is not null or by pct_orig if pct_cur is null. there are multiple records of pct_cur and pct_orig. So if in one record pct_cur is null, I want to use pct_orig for the sum of pct:

    example:
    loan number amount pct_cur pct_orig
    100 1000 .5 .3
    100 1000 null .4
    100 1000 .2 .1

    so in the above case the sum of pct would be 1.1 (.5+.4+.2) so how do I put that in SQL?

    I tried
    select sum(amount)
    from table1
    where sum(pct)>1
    case
    when pct_cur is not null
    then pct_cur
    when pct_cur is null
    then pct_orig
    else null
    end as pct

    can someone please tell me the proper way to do this?
    any help would be much appreciated.

  2. #2
    Join Date
    Dec 2002
    Posts
    134

    Re: Help with using Case statement in a calculation

    Check the docs for coalesce function:
    select sum(coalesce(pct_cur, pct_orig))
    from table1

  3. #3
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    You should have a good look at the SQL cookbooks in order to get better grip of SQL syntax. (As Chuzhoi recommended)

    If you are using an aggregate combined with case you wrap the aggregate around the case statement like:

    select sum(case when pct_pur is null then pct_orig else pct_pur end)

    coalesce is spot-on for your specific 'case'. You can spare 3 positions by using the 'VALUE' function (same as coalesce)
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  4. #4
    Join Date
    Sep 2003
    Posts
    10
    Thanks guys,
    actually I am a CPA (finance guy) and just started using sql last week to do some analysis..... so just making progress pretty slowly

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •