question

I have a table named Movie, with actors attribute.

actors_type is specific and looks like this:

ACTOR_TYPE is implemented as a varray(5) of varchar(20)

GEORGE.ACTOR_TYPE('Clint Eastwood', 'Christopher Carley', 'Bee Vang', 'Ahney Her')

the query I tried to count the number of movies for each actor is :

select m.title, a.column_value, count(m.title) 
    
    from movie m, table(m.actors) a  
    
    group by m.title,  a.column_value 
    
    order by a.COLUMN_VALUE

which gives me a count of each row(?) Not the count of movies for each actor. the output is as below:

what I am trying to get is to List actors that acted in multiple movies and show movie title and the actor. but when I add m.title in the select statement, it will count each row. This is the other query I wrote:

select a.column_value, count(m.title)  
    from movie m, table(m.actors) a  
    having count(m.title) > 1 
    group by a.column_value  
    order by a.COLUMN_VALUE 

which gives me a table of COLUMN_VALUE that is a actor name and count of movies acted in, which is 2.

these are correct but I need to add the title to the output too, but when I add it, all the counts will be one, as the first output table.

There is no table for Actors, we create table for it via `table(m.actors) a` to access its items.