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 |
+--------------------------------+

• Jonathan Fite: Using PIVOT would be the best way to solve this, but you don't specify the specific RDBMS that you are using. Otherwise, the solution by Balaza Papp will work for you.

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
;