# PostgreSQL ntile() partition

i have a point grid with population values. each point has an id and population value. i also have an state_id which say in what state the point is.

now i want to calculate the percentile ntile(100) for each state.

``````   SELECT  id, population, state_id,
ntile(100) OVER(ORDER BY car20) as percentile
FROM avi_threshold01
``````

when i use this, i think it calculates the ntile over all points and states.

• I know this is an old post but it's really important to understand for anyone who stumbles across this - WHAT YOU ARE RETURNING IS NOT A PERCENTILE! NTILE cannot be used to accurately calculate a percentile. Your query probably returns the correct results most of the time but it's by coincidence, not because you're math is correct. Note this article: sqlservercentral.com/articles/PERCENT_RANK/141532 specifically the section titled "How NOT to calculate Percentile Rank." The article is about MS SQL Server's NTILE but the math applies to PostgreSQL as well. Feb 8, 2019 at 19:33

If you want percentiles per `state`, then use `PARTITION BY state_id` in the `OVER` clause.

And that `GROUP BY` looks spurious at least. I think it needs to be removed if you want percentiles. And group by the PK is a no-operation anyway.

``````   SELECT
id, population, state_id,
ntile(100) OVER (PARTITION BY state_id ORDER BY car20) AS percentile
FROM
avi_threshold01 ;
``````
• thanks, looks good. i kind of hoped group by does the partition ;) May 8, 2016 at 20:01

I know this is an old post but NTILE cannot accurately calculate a percentile. The misguided idea that you can use NTILE(100) for this is a fiction that spans multiple SQL dialects; I see this often in the Microsoft T-SQL world.

Consider this query:

``````WITH sampleData as (SELECT v FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(2),(2),(2)) as x(v))
SELECT V, NTILE (2) OVER(ORDER BY v) AS nt
FROM sampleData;
``````

According to the laws of mathematics, identical values will always have the same percentile value. For example, if Mary's test score was 76 and Fred's was also 76, and Mary scored in the top 2% percentile - then so did Fred!

In the sample data above there's seven 1's and three two's. Yet the NTILE column returns five 1's and five 2's. This mathmatically proves my above statement about NTILE(100). NTILE(100) is cannot calculate percentiles because NTILE distributes rows evenly.

I've said this before but it's worth repeating: NTILE and Percent_rank are not related in any way, they are not even members of the same family of ANSI SQL functions. NTILE is a window ranking function and does not consider ties; NTILE exists to do one thing only: divide rows up evenly. Percent_Rank, on the other hand, is a rank distribution function and will ONLY assign a duplicate value to two values when they are identical. Huge difference. Yes, there are cases where NTILE(100) will return the correct answer but its coincidental; note this article: Nasty Fast Percent Rank