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.