david mah
david mah

Reputation: 315

How to list the amount of movies each actor has starred in

I have a table which lists out the movies that a certain actor has starred in. The table looks something like this:

      CELEB          | MOVIE
------------------------------------------
Pamela Anderson      | Borat: Cultural Learnings of America for Make Benefit Glorious Nation of Kazakhstan
Pamela Anderson      | The Flintstones: Little Big League
Pamela Anderson      | Pauly Shore Is Dead
Pamela Anderson      | Roast of Pamela Anderson: Uncensored
Pamela Anderson      | Snapdragon
Jerry Seinfeld       | The Ratings Game
Jerry Seinfeld       | Pros & Cons
Jerry Seinfeld       | Hindsight Is 20/20

And the list would go on for every movie each celebrity has starred in. Now I'm trying to write sql query to find the amount of movies that each actor as starred in. I know how to list out all the movies that a specific actor has done:

Select COUNT(MOVIE)
From MOVIES_TABLE
Where CELEB = "Jerry Seinfeld"

But I don't know how to list out the number of movies each actor has done and then list them in descending order.

Upvotes: 1

Views: 6160

Answers (2)

dana
dana

Reputation: 18105

GROUP BY allows you to get the aggregate data per celeb. COUNT(*) gives you the total #.

select CELEB, count(*) as NUM_MOVIES
from MOVIES_TABLE
group by CELEB
order by NUM_MOVIES desc

Upvotes: 2

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

Just GROUP BY by celeb to find count for each celebrity and then sort the count in descending order like this:

select celeb, count(*) number_of_movies
from movies
group by celeb
order by number_of_movies desc;

Upvotes: 1

Related Questions