Aggregate function to count occurrences of specific word
Sure, have 3 SUMs of the needed values converted to 1, and others to 0.

select
  id,
  sum(case when enum = 'A' then 1 else 0 end) as a_count,
  sum(case when enum = 'B' then 1 else 0 end) as b_count,
  sum(case when enum = 'C' then 1 else 0 end) as c_count
from
  table
group by
  id
;