Hello,

Problem Statement:

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.

Details:

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

**pets**

cat, fish, dog

cat, dog

dog

dog, bird, fish

fish, rat, cat

I need to produce a count of vet clinics that will see each pet type.

**Desired output**

bird | 1

cat | 3

dog | 4

fish | 3

rat | 1

Thank you in advance.