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.