Group by for quartiles

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a dataset that I need to calculate quarties for. This data set
contains about 20K rows with 200 or so job titles. I want to be able to
calculate the first quartile for each job title.

I have seen some posts on calculating the quartile for a given dataset, but
not on how to do for multiple categories, in this case, job titles.

Thoughts?

PJ
 
I assume you want to get the limit of the first quartile, per job.

I would define the first quartile value as the minimum value that is still
in the TOP 25 PERCENT largest values

I doubt that a TOP 25 percent would be fast enough, so I would rather use a
RANKing technique:


SELECT a.jobID, a.fieldToQuartile AS inFirstQuartile
FROM myTable AS a INNER JOIN myTable AS b
ON a.jobID = b.jobID AND a.fieldToQuartile >= b.fieldToQuartile
GROUP BY a.jobID, a.fieldToQuartile
HAVING COUNT(*) <= 0.25 * (SELECT COUNT(*)
FROM myTable
WHERE jobID= a.jobID)




is a query that should return ALL rows which belong to the first quartile,
for each job. If that is what you want, stop there.

Make another query, using that query, to find the MIN(inFirstQuartile), per
job,if you just want the limit, not all the values in the top quartile.

Change the >= to <= for the lowest quartile (example, jobID having the
lesser number of errors, as example, rather than those having the highest
something).


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top