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.
Answer by Jon Crawford ·
since you are retrieving the title and grouping on the title, the count will always be 1, because you are counting the title.
Your second query looks good, think about how you would turn that into a subquery and make sure your actors are in that list of people with 2 or more movies.