how to count repeated values separated by commas from two separated tables in postgres sql-CodePudding

I can't figure out how to do this task. I have two tables with the same format and I have to count the top 10 most repeated actors considering the year taking the data from both tables. the tables looks like this. and as you can see the column cast contains values separated by commas.

I did it in python, and it looks like this:

[(' Anupam Kher', 39), (' Rupa Bhimani', 27), (' Om Puri', 27), ('Shah Rukh Khan', 26), (' Boman Irani', 25), (' Paresh Rawal', 25), (' Julie Tejwani', 24), ('Akshay Kumar', 23), (' Jim Cummings', 23), ('Adam Sandler', 21)]

CodePudding user response：

well, I figured out how to do it (in my own way) because I have too little experience in sql

first of all I create a table with the needed information from the two tables:

create table multiple as
(select casting, release_year from public."Tabla_Disney"
union
select casting, release_year from public."Tabla_Netflix")

then I splitted the values from the column then i count them

SELECT count(casting) as my_count, casting
FROM (SELECT unnest(string_to_array(casting, ',')) AS casting
    FROM public.multiple where release_year = '2021') AS casting
GROUP BY casting
order by my_count desc
LIMIT 10

The result:

7 " Fortune Feimster" 6 " London Hughes" 6 "David Spade" 5 " Grey Griffin" 4 " Fred Tatasciore" 4 " Kari Wahlgren" 4 " Tress MacNeille" 4 " Wataru Takagi" 3 " Aditi Rao Hydari" 3 " Dee Bradley Baker"