Postgres: How do I count occurrences of each enum value when they exist in columns as an array?

I have an enum State which can contain values like CA, NY, etc.

If I have a table Users , with a column states that contains an array of State values, so for example {CA, NY} how can I write a query to count the users grouped by each State value? so for {CA, NY} that should count 1 for CA and 1 for NY

So If I had records like:

| id | states  |
| -- | ------- |
| 1  | {CA,NY} |
| 2  | {CA}    |
| 3  | {NV,CA} |

I would expect a query to output:

| State | count |
| ----- | ----- |
| CA    | 3     |
| NV    | 1     |
| NY    | 1     |

Answer

The first piece of advice is to normalise your data. You are breaking 2nd Normal form by holding multiple pieces of information in a single column.

Assuming you can’t change that, then you will need to SPLIT the data like this enter link description here

and you can then COUNT() and group it.