0

I have a table named Movie, with actors attribute. actors_type is specific and looks like this: 

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

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

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:

enter image description here

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

and the result is:

enter image description here

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

Movie Table: enter image description here

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

Samantha
  • 71
  • 1
  • 7

1 Answers1

0

You have to remove m.title from group by and selection cause you are going to count that according to actor.I assume column_value is a common column between two tables , so i used that on join You need to try like below

select  a.column_value, count(m.title)     
from movie m
join m.actors a  on m.column_value=a.column_value    
group by  a.column_value     
order by a.COLUMN_VALUE
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
  • I got this error "table or view does not exist." I think there is no m.column_value, cuz the Movie table is a simple entity table. We use COLUMN_VALUE to access the items inside the collection or varray table that we create. there is not such a column in movie table – Samantha Oct 10 '18 at 09:09
  • @Jasmine then pleases share two tables available all column – Zaynul Abadin Tuhin Oct 10 '18 at 09:10