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:

  ROUND(100.0 * (SELECT COUNT(*) FROM film AS f2 WHERE f2.length <= f.length) / totals.film_count, 1) AS percentile
FROM film f
  SELECT COUNT(*) AS film_count
  FROM film
) AS totals
ORDER BY percentile DESC;
database icon
Shared queries and folders ✅ Version history ✅ One-click connection to MySQL ✅
Get more done, together, with PopSQL and MySQL