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

Do a Self cross join whenever you want to get manipulated data from same table.

SELECT
m.actor,
COUNT(m.actor) AS total,
(COUNT(m.actor) / t.total_movies) * 100 AS avg
FROM movies_actors m
cross (select count(*) as total_movies from movies_actors) t
GROUP BY m.actor;

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.

Tags:

Mysql

Sql