Introducing PopSQL + dbt. The first SQL editor with built-in dbt supportRead more →

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;

Ready for a modern SQL editor?