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!


.



Relevant Pages

  • Re: Calculating quartiles in a query
    ... Score ProjectNo ... one for the second quartile). ... >> and you would be able to continue to use the graphical part of the query ... If you need to create a table, in the designer, change the ...
    (microsoft.public.access.queries)
  • Re: Median and Quartiles
    ... QuartileX query, it gave me an error message "The action query ... RankedJobCodeRates and followed your instructions from there. ... rank, jobCode and HourlyRate. ... for third quartile. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Calculating quartiles in a query
    ... and you would be able to continue to use the graphical part of the query ... ""quartile table" is required. ... >> Vanderghast, Access MVP ...
    (microsoft.public.access.queries)
  • Re: Calculating quartiles in a query
    ... suggestion. ... up in the SQL view of a new query? ... > For the first and last quartile, ... >> Need assistance in automating the process of defining quartiles in a ...
    (microsoft.public.access.queries)