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

mysql, sql

Solution

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.

Problem

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?

Original source