0

I have a column from a SQL Table with the values (they're from a movie database):

type

drama
comedy, romance
comedy
thriller, short
drama, romance

I want to separate and count them, with an output like:

type  count

drama 2
comedy 2
thriller 1
short 1
romance 2

how would you do this in the most simple-fastest way possible?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Bárbara
  • 1
  • Welcome to SO, Bárbara! What have you tried so far? – istepaniuk Oct 25 '20 at 20:29
  • 5
    In the simplest possible way? I would have normalized the database and not put multple values into a single attribute. Anything else will be messy. Not least because CSV is a family of formats). – symcbean Oct 25 '20 at 20:32
  • sql isn't made for this, so you have to use a stored procedure for this – nbk Oct 25 '20 at 20:43
  • how many types will a movie have at most? what database version are you using? – ysth Oct 25 '20 at 20:45
  • 1
    You *can* use a stored procedure for this, but you don't *have to* – Strawberry Oct 25 '20 at 21:01
  • 1
    For related issues, see my answer to [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) – Bill Karwin Oct 25 '20 at 22:16

1 Answers1

0

Ideally you would use a separate table movie_type with one row per type per movie. Otherwise, you need to use substring_index to pick out each type and join to an ad hoc table (or a recursive cte) identifying which type to get from each row:

select type, count(*) from (
    select substring_index(substring_index(type, ', ', num),', ',-1) type
    from movie
    join (
        select 1 num union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9
    ) nums on num <= (length(type)-length(replace(type,', ','')))/length(', ')+1
) movie_types
group by type;

(length(foo)-length(replace(foo,'bar','')))/length('bar') is the count of how many times 'bar' appears in foo. You add one to the count of ', ' in type to get how many types are listed.

fiddle

ysth
  • 96,171
  • 6
  • 121
  • 214