Aggregate function to count occurrences of specific word

Question

I have a table upon which I'm performing a GROUP BY statement. One of the columns in my table can contain one of 5 different words (essentially an enumerated type). Is there a way I can use an aggregate to get the occurrences of each enum, but display each count in a new column?

For example, given the table:

+---------+
|id | enum|
+---------+
| 1 | A   |
| 1 | B   |
| 1 | C   |
| 1 | A   |
+---------+

I'd like to get something like this after grouping by id:

+------------+---------+---------+
|id | a_count| b_count | c_count |
+------------+---------+---------+
| 1 |   2    |    1    |    1    |
+--------------------------------+

Answer 1

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
;