I am trying create an query (Redshift) that will count the number of times each value appears in a column that contains comma separated values.
- The possible values in the CSV column are predefined.
- There are 14 possible values, I know these without needing to refer to the data
- Each of the 14 values can only appear in the CSV column at most once
- Each record has at least one of the 14 values in the CSV column, but could contain many (up to 14).
I tried adding tally/counter columns to my view that would record: 1 (CSV column has value) or 0 (CSV column doesn't have value)
case when [column] like '%value1%' then 1 else 0 as value_1, case when [column] like '%value2%' then 1 else 0 as value_2, etc.
This worked in the sense that now I have columns that indicate how many true or false whether each value is in the CSV column, however I'm struggling to summarize these tally columns in a way that gives me a count for each distinct value. Any ideas? (or maybe I'm going about this all wrong?)
as an example, the column below is represents my CSV column for a veterinarian table. the column 'pets' represent the type of pet each vet clinic works with.
cat, fish, dog
dog, bird, fish
fish, rat, cat
I need to produce a count of vet clinics that will see each pet type.
bird | 1
cat | 3
dog | 4
fish | 3
rat | 1
Thank you in advance.