CODEWITHSUNDEEP
sqlms-accessms-access-2010
Chris2015
Chris2015

Reputation: 1050

Returning a variety of percentiles in SQL

I have a piece of code which returns the 90th percentile of a group of data and I am trying to change the code to return other percentiles (25th, 50th, 75th). Here is the code which returns the 90th percentile (without interpolating) successfully: 

SELECT Max ([Subquery]![RECP]) AS RECP90P
FROM (SELECT TOP 90 PERCENT [RECP] FROM [CompData Query] ORDER BY [RECP] DESC)  AS Subquery;

My thinking is that if I change the code to the following I would be able to return the 25th percentile, for instance. Here is what I've attempted:

SELECT Min ([Subquery]![RECP]) AS RECP25P
FROM (SELECT TOP 75 PERCENT [RECP] FROM [CompData Query] ORDER BY [RECP] DESC)  AS Subquery;

I know that there is no SELECT BOTTOM in SQL so I was attempting to return the inverse by using MIN. However, when I attempt to run this code I return a blank value. I tried changing DESC to ASC thinking that perhaps MINwas returning the first value and not the minimum of all values, but even this returned a blank value. Is anyone able to explain where I am going wrong?

Would something like this work instead? 

SELECT Max([CompData Query]![AgedSalary]) CASE ([CompData Query]![Code]) WHEN "RECP" AS RECP90P
FROM (SELECT TOP 90 PERCENT [AgedSalary] FROM [CompData Query] ORDER BY [RECP] DESC)  AS Subquery;

Would you be able to explain why this CASE WHENis wrong and/or missing an `Operator':

SELECT CASE ([CompData Query]![Code]) WHEN "RECP" THEN SELECT Max([CompData Query]![AgedSalary]) AS RECP90P FROM (SELECT TOP 90 PERCENT [AgedSalary] FROM [CompData Query] ORDER BY [AgedSalary] DESC) ELSE 'Unknown' END;

Thank you.

Upvotes: 2

Views: 333

Answers (1)

shawnt00
shawnt00

Reputation: 17925

I think Access has a TRIM() function but any of LTRIM, RTRIM, and TRIM should do what you want.

SELECT Min ([Subquery]![RECP]) AS RECP25P
FROM (
    SELECT TOP 75 PERCENT [RECP]
    FROM [CompData Query]
    WHERE RTRIM([RECP]) <> ''
    ORDER BY [RECP] DESC
)  AS Subquery;

Upvotes: 1

Related Questions