[SOLVED] MySQL Sakila Select actor with the most films

MySQL Sakila Select actor with the most films

I need to select the actor which appeared in the most films. I am using the sakila Database 1.0:

.mwb (MySQL Workbench): https://www.sendspace.com/file/i0z82j
.sql (schema): https://www.sendspace.com/file/vd3hnu
.sql (data): https://www.sendspace.com/file/gbp9ri

What I already have is two queries

which selects every actor n times (n=appearances in films). My query:

SELECT actor.first_name, actor.last_name
FROM actor
INNER JOIN film_actor
ON actor.actor_id = film_actor.actor_id;

Result:

+------------+-------------+
| first_name | last_name   |
+------------+-------------+
| PENELOPE   |GUINESS      | //4 Films in this example
| PENELOPE   |GUINESS      |
| PENELOPE   |GUINESS      |
| PENELOPE   |GUINESS      |
| NICK       |WAHLBERG     | //5 Films in this example
| NICK       |WAHLBERG     |
| NICK       |WAHLBERG     |
| NICK       |WAHLBERG     |
| NICK       |WAHLBERG     |
| ED         |CHASE        | //5 Films in this example
| ED         |CHASE        |
| ED         |CHASE        |
| ED         |CHASE        |
| ED         |CHASE        |
| JENNIFER   |DAVIS        | //4 Films in this example
| JENNIFER   |DAVIS        |
| JENNIFER   |DAVIS        |
| JENNIFER   |DAVIS        |
| BETTE      |NICHOLSON    | //3 Films in this example
| BETTE      |NICHOLSON    |
| BETTE      |NICHOLSON    |
| ...        |...          | //Every other Actor who appeared in a film
+------------+-------------+

which selects every film a specific actor appeared in. My query:

SELECT actor_id,film_id
FROM film_actor
WHERE actor_id = 1;

Result: Actor 1 appeared in 19 films.

+----------+-------- +
| actor_id | film_id |
+----------+---------+
| 1        | 1       |
| 1        | 23      |
| 1        | 25      |
| 1        | 106     |
| 1        | 140     |
| 1        | 166     |
| 1        | 277     |
| 1        | 361     |
| 1        | 438     |
| 1        | 499     |
| 1        | 506     |
| 1        | 509     |
| 1        | 605     |
| 1        | 635     |
| 1        | 749     |
| 1        | 832     |
| 1        | 939     |
| 1        | 970     |
| 1        | 980     |
+----------+---------+

But what I want is: The actor who appeared in the most films:

+------------+---------------+--------+
| first_name | last_name     | films  |
+------------+---------------+--------+
| NICK       | WAHLBERG      | 5      |
| ED         | CHASE         | 5      |
| ...        | ...           | ...    |
+------------+----------------+-------+

Any help is highly appreciated. Thank You!

Solution

Without being able to run the query on your db I'd do:

SELECT count(film_actor.actor_id), actor.first_name, actor.last_name 
FROM actor INNER JOIN film_actor ON actor.actor_id = film_actor.actor_id
GROUP BY film_actor.actor_id ;

Untested