CODEWITHSUNDEEP
mysqlsql
Chamindar2002
Chamindar2002

Reputation: 223

Mysql subtract two row values from group by

I am using the following query to get the results as two rows for earnings and deductions.

select t2.type
     , sum(t1.amount) as total_earnings 
  from employee_pay_elements t1
  join pay_elements t2 
    on (t1.pay_element_id = t2.id)
 group 
    by t2.type

and the result

enter image description here

Now I need to get the difference (EARNING - DEDUCTION) how do I get the diff easily and efficiently?

Thank you in advance

Upvotes: 0

Views: 183

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use conditional aggregation:

select sum(case when pe.type = 'EARNING' then epe.amount
                when pe.type = 'DEDUCTION' then - epe.amount
            end) as total_earnings
from employee_pay_elements epe join
     pay_elements pe
     on epe.pay_element_id = pe.id;

Upvotes: 1

Related Questions