I have two tables A and B as below. Table A
slip | component | amount |
---|---|---|
ss-001 | PF | 1500 |
ss-001 | PT | 200 |
ss-002 | PF | 1450 |
ss-002 | PT | 150 |
ss-003 | PF | 1550 |
ss-003 | PT | 300 |
Table B
emp | slip | netpay |
---|---|---|
emp-001 | ss-001 | 18500 |
emp-002 | ss-002 | 17000 |
emp-003 | ss-003 | 16600 |
how may I get below Table through query?
emp | PF + PT | netpay |
---|---|---|
emp-001 | 1700 | 18500 |
emp-002 | 1600 | 17000 |
emp-003 | 1850 | 16600 |
I am able to get PF and PT in two different columns but unable to SUM.
I tried SUM()
and CONCAT()
.
This can be done using the conditional aggregation as follows :
select emp, sum(case when component = 'PF' then amount end)
+ sum(case when component = 'PT' then amount end) as 'PF + PT',
max(netpay) as netpay
from tableA a
inner join tableB b on a.slip = b.slip
group by emp;
Results :
emp PF + PT netpay
emp-001 1700 18500
emp-002 1600 17000
emp-003 1850 16600