2

I want to calculate the percentile in MYSQL. Please let me know this is the right way to calculate the percentile. 

Set @percentile = 30;
select firnum, a.rownum
from 
     (select firnum, @rownum := @rownum + 1 AS rownum
      from (select firnum from per order by firnum) as a, 
           (SELECT @rownum := 0) b) as a,
           (select round((COUNT(*) * @percentile)/100) percen from per) as b
where a.rownum = b.percen;
McNets
  • 10,352
  • 3
  • 32
  • 61
user3738664
  • 107
  • 3
  • 13
  • Possible duplicate of [Calculate Percentile Value using MySQL](https://stackoverflow.com/questions/19770026/calculate-percentile-value-using-mysql) – Lukas Eder Jan 28 '19 at 09:51

1 Answers1

0

I was trying to solve this for quite some time and then I found the following answer. Honestly brilliant. Also quite fast even for big tables (the table where I used it contained approx 5 mil records and needed a couple of seconds).

SELECT
 CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(
  GROUP_CONCAT(field_name ORDER BY field_name SEPARATOR ','),
   ',', 95/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS '95th Per'
 FROM table_name;

As you can imagine just replace table_name and field_name with your table's and column's names.

For further information check Roland Bouman's original post

tony_pets
  • 23
  • 5