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.