Calculate Percentile Value using MySQL

mysql percentile
Daniel C picture Daniel C · Nov 4, 2013 · Viewed 14.8k times · Source

I have a table which contains thousands of rows and I would like to calculate the 90th percentile for one of the fields, called 'round'.

For example, select the value of round which is at the 90th percentile.

I don't see a straightforward way to do this in MySQL.

Can somebody provide some suggestions as to how I may start this sort of calculation?

Thank you!

Answer

Abbas Hosseini picture Abbas Hosseini · Jul 28, 2016

First, lets assume that you have a table with a value column. You want to get the row with 95th percentile value. In other words, you are looking for a value that is bigger than 95 percent of all values.
Here is a simple answer:

SELECT * FROM 
(SELECT t.*,  @row_num :=@row_num + 1 AS row_num FROM YOUR_TABLE t, 
    (SELECT @row_num:=0) counter ORDER BY YOUR_VALUE_COLUMN) 
temp WHERE temp.row_num = ROUND (.95* @row_num);

Related questions

Calculating percentile rank in MySQL

I have a very big table of measurement data in MySQL and I need to compute the percentile rank for each and every one of these values. Oracle appears to have a function called percent_rank but I can't find …

Read More
Quartiles in SQL query

I have a very simple table like that: CREATE TABLE IF NOT EXISTS LuxLog ( Sensor TINYINT, Lux INT, PRIMARY KEY(Sensor) ) It contains thousands of logs from different sensors. I would like to have Q1 and Q3 for all sensors. …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More