select
entries.Serial_Number
, sum( switch(
entries.Status = 'ADD', 1,
entries.Status = 'CAN', 0,
entries.Status = 'DEL', 0
)
) as [Added]
, sum( switch(
entries.Status = 'ADD', 0,
entries.Status = 'CAN', 1,
entries.Status = 'DEL', 0
)
) as [Cancelled]
, sum( switch(
entries.Status = 'ADD', 0,
entries.Status = 'CAN', 0,
entries.Status = 'DEL', 1
)
) as [Deleted]
from HVAC_Duplicate_Entries as entries
group by entries.Serial_Number;