Below is the snippet of my data,
empid CategoryName Type value 1 Accommodation Deduction DEDUCTION -100.0000 1 Canteen Deduction DEDUCTION -200.0000 1 Housing ADDITION 3000.0000 1 Transport Allowance ADDITION 2000.0000
empid would be employee id and there are other employee id’s in the dataset but all will have similar data.
My aim to to show the various pay categories as columns in the query result like,
Accommodation Deduction Canteen Deduction Housing Transport Allowance -100.0000 -200.0000 3000.0000 2000.0000
I am using the below query,
select MAX(CASE WHEN CategoryName = 'Accommodation Deduction' THEN pi.value ELSE 0 END) AS 'Accommodation Deduction', MAX(CASE WHEN CategoryName = 'Canteen Deduction' THEN pi.value ELSE 0 END) AS 'Canteen Deduction', MAX(CASE WHEN CategoryName = 'Housing' THEN pi.value ELSE 0 END) AS 'Housing', MAX(CASE WHEN CategoryName = 'Transport Allowance' THEN pi.value ELSE 0 END) AS 'Transport Allowance' from Report pi group by empid;
The deduction category are showing value as zero which should not be,
Accommodation Deduction Canteen Deduction Housing Transport Allowance 0.0000 0.0000 3000.0000 2000.0000
Advertisement
Answer
There are columns whose values are negative, so the conditionl MAX()
returns 0
, because of the ELSE 0
branch of the CASE
statement.
You can turn these to MIN()
s:
select empid MIN(CASE WHEN CategoryName = 'Accommodation Deduction' THEN pi.value ELSE 0 END) AS Accommodation_Deduction, MIN(CASE WHEN CategoryName = 'Canteen Deduction' THEN pi.value ELSE 0 END) AS Canteen_Deduction, MAX(CASE WHEN CategoryName = 'Housing' THEN pi.value ELSE 0 END) AS Housing, MAX(CASE WHEN CategoryName = 'Transport Allowance' THEN pi.value ELSE 0 END) AS Transport_Allowance from Report pi group by empid;