How to count two different values in the same column and output those counts to two different columns

So I have this code already.

select
Item,
count(WORK_TYPE) AS 'Capacity Replen'
from WORK_INSTRUCTION
where WORK_TYPE = 'Replen - Capacity'
Group by ITEM

Which outputs this:

Item    Capacity Replen
E000191208  3
E000191904  2
E000328017  2
E000397711  2

I need to be able to count a different Work_Type as well and output that count to the associated item.

Answer

I think you’re looking for conditional aggregation.

SELECT 
   Item
  ,SUM( CASE WHEN WORK_TYPE= 'Replen - Capacity' THEN 1 ELSE 0 END) AS 'Capacity Replen'
  ,SUM( CASE WHEN WORK_TYPE= 'Some Other Criteria' THEN 1 ELSE 0 END) AS 'Some Other Column Name'  
FROM WORK_INSTRUCTION
WHERE WORK_TYPE IN ('Replen - Capacity','Some Other Criteria')
GROUP BY ITEM