How to Calculate Percentiles in MySQL

MySQL is still behind other databases when it comes to analytical/window functions. But there are ways to get things done. For example, to get a top-down percentile ranking of film lengths from the Sakila Sample Database:

SELECT
  f.title,
  ROUND(100.0 * (SELECT COUNT(*) FROM film AS f2 WHERE f2.length <= f.length) / totals.film_count, 1) AS percentile
FROM film f
CROSS JOIN (
  SELECT COUNT(*) AS film_count
  FROM film
) AS totals
ORDER BY percentile DESC;
database icon
Real-time SQL collaboration is here
Get started with PopSQL and MySQL in minutes