question

jasmine22 avatar image
jasmine22 asked

How to count number of items in a specific type in a table in sql?

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.

sqlcounttype
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Jon Crawford avatar image
Jon Crawford answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.