How to get a percentage of total when the query has a GROUP BY?

Question

Say I have a non-normalized table with movie actor names and the movies they've been in. eg.

CREATE TABLE movies_actors (   movies_actors_id INT,   movie VARCHAR(255),   actor VARCHAR(255),   PRIMARY KEY (movies_actors_id) ); 

I do a SELECT actor, COUNT(1) FROM movies_actors GROUP BY actor to find out how many movies the actor has been in. But I also want to find out what percentage of movies that actor has been in.

I guess I could do this:

SELECT   actor,   COUNT(1) AS total,   COUNT(1) / (SELECT COUNT(1) FROM movies_actors) * 100 AS avg FROM movies_actors GROUP BY actor; 

But that just seems... idk... yucky.

Any ideas?

Answer 1

For large sets, a JOIN may perform better than the subquery.

SELECT ma.actor      , COUNT(1) AS total      , COUNT(1) / t.cnt * 100 AS `percentage`   FROM movies_actors ma  CROSS   JOIN (SELECT COUNT(1) AS cnt FROM movies_actors) t  GROUP     BY ma.actor      , t.cnt   

For large sets, and when a large percentage of the rows are being returned, the JOIN operation can usually outperform a subquery. In your case, it's not a correlated subquery, so MySQL shouldn't have to execute that multiple times, so it may not make any difference.

Note to non-fans of COUNT(1)... we could replace any and all occurrences of COUNT(1) with COUNT(*) or IFNULL(SUM(1),0) to achieve equivalent result.