Re: QUARTILES in SQL/QUERY



If you define the x-percentile as the lowest value for which at least x% of
the records have a value less or equal to it, that is, t

he RANK of the record should be >= (x/100) * Number of record


So, using join to compute the rank (assuming there is no duplicated value
for the field used to rank records):


SELECT a.primaryKey, COUNT(*) AS rank
FROM table AS a INNER JOIN table AS b
ON a.fieldToRank >= b.fieldToRank
GROUP BY a.primaryKey


does just that. To get all the records past the x-percentile (we take the
primary key and the value defining the ranking position):


SELECT a.primaryKey, LAST(a.fieldToRank)
FROM table AS a INNER JOIN table AS b
ON a.fieldToRank >= b.fieldToRank
GROUP BY a.primaryKey
HAVING COUNT(*) >= [x] *(SELECT COUNT(*) FROM table)



or to just get the value itself making the 'breaking' point:


SELECT MIN(y.theValue)
FROM (
SELECT LAST(a.fieldToRank) AS theValue
FROM table AS a INNER JOIN table AS b
ON a.fieldToRank >= b.fieldToRank
GROUP BY a.primaryKey
HAVING COUNT(*) >= x *(SELECT COUNT(*) FROM table)
) AS y




(you may have to write that query in two parts: save the inner most query,
then use SELECT MIN(y.theValue) FROM savedQuery As y)




Vanderghast, Access MVP




"BlueWolverine" <BlueWolverine@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:295A9AA7-1F75-4650-964B-711B7EB46F56@xxxxxxxxxxxxxxxx
Hey,
MS Access 2003 in XP PRO.

I'm evaluating personel and I need to divide a rating into quartiles,
preferably in a query, not filling a table tiwh VB, though if that's the
only
way, then so be it.

Field 1: Driver ID
FIeld 2: Count of Issues

I want Field 3: to be the quartile of the distribution of the Count of
Issues. I want to be able to pick out top quartile, second quartile, 3rd
quartile, bottom quartile for each driver. I know what I want to do with
that info once I have it, I just can't figure out how to do it.

Thanks.


--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


.