# Thread: Help with using Case statement in a calculation

1. Registered User
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. Registered User
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. Registered User
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)

4. Registered User
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
•