1

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     |
myoung
  • 373
  • 2
  • 6

1 Answers1

2

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.

Aaron Reese
  • 544
  • 6
  • 18
  • 1
    You should take some time and study [data normalization](https://database.guide/what-is-normalization/) (that is just 1 of hundreds available). By the way you are actually violating 1st Normal form: "each attribute contains only a **single value**" from a domain. – Belayer Apr 17 '22 at 17:55
  • Ok so you would suggest I create a join table User_States even though the States are just an enum and not actually a table of its own? – myoung Apr 17 '22 at 23:18
  • 1
    The short answer is yes. ENUMs are just a shorthand for a lookup or domain table. Longer term it may make more sense to introduce a domain table as intruduces better control and adjustment. E.g. if you want to add more states (lets say you expand into Candada) you may need to be able to group or Super-Type the states by territory. If you need to change the list of states, currently you would have to edit the code to update enum and recompile the entire code base that uses it and do a full regression test and code deployment. – Aaron Reese Apr 18 '22 at 10:19
  • 1
    If it is just an entry in the domain table, you can add it in using your RDBMs management tools. What would you currently do if a state was 'retired' for new records but still needed to be valid for previous records. – Aaron Reese Apr 18 '22 at 10:19
  • That's super helpful, thank you. Sad to say, actually never heard of 1NF and 2NF until your comments. Will try to adhere to these normalization rules better! – myoung Apr 20 '22 at 04:49