0

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    |
+--------------------------------+
  • 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. – Jonathan Fite Apr 8 '16 at 18:33
2

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
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.