CODEWITHSUNDEEP
sqloracle-databasecase
Micah E
Micah E

Reputation: 11

Case Statement having no effect on output

I am trying to get sums of donations based on the bank approval status and grouped by gift kind. However, it the script outputs RR and NR donations on separate lines (see below the script). It seems like the case statements aren't working at all.

select gift_kind,
    case
        when c.bank_approval_status = 'AP' then 
            sum(c.charge_amount) end approved,  
    case    
        when c.bank_approval_status in ('RR','NR') then 
            sum(c.charge_amount) end rejected,
    case    
        when c.bank_approval_status = 'AR' then 
            sum(c.charge_amount)*-1 end refunded,
    case 
        when c.bank_approval_status not in ('AR','AP','RR','NR')  then 
            sum(c.charge_amount) end other_status
from charge_log c, transactions t
where c.account_id=t.account_id
and c.process_id= 'CHG - 02532'
and c.gift_date=t.gift_date
and c.gift_seq=t.gift_seq
and C.PLEDGE_NUMBER=t.pledge_number
and t.sts='A'
group by  t.fund_type, t.gift_kind, c.bank_approval_status
order by gift_kind asc

Upvotes: 1

Views: 42

Answers (2)

trincot
trincot

Reputation: 350147

You need to put the case clauses inside the sums, and add else 0 to make sure you don't get null as a result:

sum(case
    when c.bank_approval_status = 'AP' then 
        c.charge_amount else 0 end) approved,  
sum(case    
    when c.bank_approval_status in ('RR','NR') then 
        c.charge_amount else 0 end) rejected,
sum(case    
    when c.bank_approval_status = 'AR' then 
        -c.charge_amount else 0 end) refunded,
sum(case 
    when c.bank_approval_status not in ('AR','AP','RR','NR')  then 
        c.charge_amount else 0 end) other_status

And your group by should rarely be on columns you use in aggregations (here bank_approval_status in the sum). Change to:

group by  gift_kind

Depending on your scheme, other fields might need to be added to the group by clause but then it would make sense to also put them in the select and order by clauses.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I believe you are looking for this logic:

select gift_kind,
       sum(case when c.bank_approval_status = 'AP' then c.charge_amount 
           end) as approved,  
       sum(case when c.bank_approval_status in ('RR', 'NR') then c.charge_amount
           end) as rejected,
       sum(case when c.bank_approval_status in ('AR') then c.charge_amount*-1
           end) as refunded,
       sum(case when c.bank_approval_status not in ('AR','AP','RR','NR') then c.charge_amount
           end) other_status
from charge_log c join
     transactions t
     on c.account_id = t.account_id and
        c.gift_date = t.gift_date and
        c.gift_seq = t.gift_seq and
        C.PLEDGE_NUMBER = t.pledge_number
where c.process_id = 'CHG - 02532' and t.sts = 'A'
group by t.fund_type, t.gift_kind
order by gift_kind asc

Notes:

  • Learn to use proper JOIN syntax. Never use commas in the FROM clause.
  • The JOIN conditions should all be in the ON clause, not the WHERE clause.
  • The CASE is an argument to the SUM().
  • Remove the bank_approval_status from the GROUP BY.
  • I don't know why fund_type is in the GROUP BY. You may have a reason for that so I left it.

Upvotes: 2

Related Questions