1

Hi exchange community,

Problem statement and details:
I am trying to query a column that contains CSV data to produce a count of instances of each value in the table. There are a total of 14 possible values in the CSV data. These are static and will never change. Each record contains at least one value in the CSV column but may contain up to all 14 values. Each value may only appear at most once in the CSV column per record.

Sample Data from Pets column:
cat, dog fish, rat
dog
dog, cat horse, ferret, zebra
dog, cat, alligator, bear, fish, bird

what have i tried?
I've gotten as far as being able to return a view that contains a single record with columns containing a count for the number of instances. It's close to what I need, I just need to have this transposed (rows <-> columns). any ideas?

SELECT 
    count(case when vet.pets like '%dog%' THEN 1 ELSE NULL END) AS dog
    ,  count(case when vet.pets like '%cat%' THEN 1 ELSE NULL END) as cat
    ,  count(case when vet.pets like '%rat%' THEN 1 ELSE NULL END) as rat
    ,  count(case when vet.pets like '%bird%' THEN 1 ELSE NULL END) as bird
    ,  count(case when vet.pets like '%fish%' THEN 1 ELSE NULL END) as fish
    ,  count(case when vet.pets like '%hamster%' THEN 1 ELSE NULL END) as hamster
    ,  count(case when vet.pets like '%ferret%' THEN 1 ELSE NULL END) as ferret
    ,  count(case when vet.pets like '%horse%' THEN 1 ELSE NULL END) as horse
    ,  count(case when vet.pets like '%zebra%' THEN 1 ELSE NULL END) as zebra
    ,  count(case when vet.pets like '%bear%' THEN 1 ELSE NULL END) as bear
    ,  count(case when vet.pets like '%alligator%' THEN 1 ELSE NULL END) as alligator
    ,  count(case when vet.pets like '%sugar_glider%' THEN 1 ELSE NULL END) as sugar_glider
    ,  count(case when vet.pets like '%tiger%' THEN 1 ELSE NULL END)  as tiger
    ,  count(case when vet.pets like '%rock%' THEN 1 ELSE NULL END) as rock
    FROM vet

This produces:

dog | cat | rat | bird | fish | hamster | ferret | horse | zebra | bear | alligator | sugar_glider | tiger | rock
3 | 5 | 2 | 6 | 8 | 1 | 8 | 5 | 1 | 3 | 2 | 2 | 4 | 2

But I need it in the format:

dog | 3
cat | 5
rat | 2
bird | 6
fish | 8
hamster | 1
ferret | 8
horse | 5
zebra | 1
bear | 3
alligator | 2
sugar_glider | 2
tiger | 4
rock | 2
1
  • Create (temporary) table with patterns (including percent signs) and join it to your table by vet.pets LIKE patterns.pattern. Remember that you will count NOT the amount of values in CSV, you will count the amount of CSVs which contains at least one (but may be more) pattern value(s) in column value (i.e. 'cat' against 'cat,dog,cat' will be counted once, not twice). If you need strictly amount you must summarise (LENGTH(vet.pets) - LENGTH(REPLACE(vet.pets, patterns.pattern, '')))/LENGTH(patterns.pattern) (in this case pattern must be without percent signs).
    – Akina
    May 18, 2020 at 5:27

1 Answer 1

1

Was able to figure this out by unioning my individual count statements.

SELECT 'dog' as 'animal', count(case when vet.pets like '%dog%' THEN 1 ELSE NULL END) AS 'count' FROM vet
UNION ALL
SELECT 'cat' as 'animal',  count(case when vet.pets like '%cat%' THEN 1 ELSE NULL END) AS 'count' FROM vet
UNION ALL
etc. 
2
  • why don't you show the query and let other people benefit from it too? May 19, 2020 at 5:44
  • 1
    @MarcelloMiorelli - added query snippet above
    – brharrii
    May 19, 2020 at 5:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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