CODEWITHSUNDEEP
sqloracle-databasemaxcase-when
Milan Pk
Milan Pk

Reputation: 77

Case when along with sum

There is an extra payer called workers comp and I am trying to making it as a Workers Comp/Liability by doing case when statement. I am trying to return a single line of row. Your help will be really appreciated.

Here is my query:

select distinct
    case when payer.class like 'Workers%' 
        then 'Workers Comp/Liability'
        else Payer.class
    end as Payer,
    sum(Payment.payment_at) AS SumOfPmt
from Payer inner join Payer.payerid = Payment.payerid 
where payer.class like 'Workers%'
Group By payer.class

Result:

Payer | SumOfPmt Workers Comp/Liability | 558.76 Workers Comp/Liability | 167036.41

What I want:

Payer | SumOfPmt Workers Comp/Liability | 167595.17

Upvotes: 0

Views: 48

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

This would be a simpler form of your query:

select 'Workers Comp/Liability' as Payer,
       sum(pm.payment_at) AS SumOfPmt
from Payer py inner join
     Payment pm
     on py.payerid = pm.payerid 
where py.class like 'Workers%';

Your WHERE clause and CASE expression are doing the same thing. Only one is necessary.

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

I would repeat the same CASE expression with GROUP BY clause :

select (case when p.class like 'Workers%' 
             then 'Workers Comp/Liability'
             else p.class
        end) as Payer,
       sum(pay.payment_at) AS SumOfPmt
from Payer p inner join 
     Payment py
     on p.payerid = py.payerid 
where p.class like 'Workers%'
group by (case when p.class like 'Workers%' 
               then 'Workers Comp/Liability'
               else p.class
          end);

You don't need to use DISTINCT since it has GROUP BY.

Upvotes: 2

Related Questions