Reputation: 315
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
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
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