Calculate quartiles and mean in mysql

Hi guys

I was wondering if there was a good way of calculating mean and lower and upper quartile in mysql? Or if not, perhaps somebody has a quick way of doing it in code? I am accessing mysql from ColdFusion.

Thanks
m3housingAsked:
Who is Participating?
 
star_trekCommented:
i can explain you in psuedo logic

1. Get all records from mysql query order by <target-field-name> asc (asc is important so that you don't have to sort the data using ColdFusion).
2. set counter = 0
3. Using the coldfusion loop, sum the <field-entries>,  increment the counter,  store the <field-entries> in an array(to use it for Quartiles), continue the loop for all records.
4. Mean = sum of all entries/counter
5. lower-Quartile = array[ceil(0.25*counter)] //you can use ceil or int
6. Upper-Quartile = array[ceil(0.75*counter)] //same as above you can use ceil, int or floor
0
 
NovaDenizenCommented:
For mean:  use AVG().  Assuming `score` is the field you want the average for.
SELECT AVG(score) FROM mytable WHERE <some condition>;

For top quartile, you need to do a few queries.  One to determine how many entries there are, then one for each entry you're looking for.

SELECT COUNT(score) from mytable;
-- say this returns 1000
SELECT score FROM mytable ORDER BY score DESC LIMIT 0, 1;   -- always 0
SELECT score FROM mytable ORDER BY score DESC LIMIT 250, 1; -- floor(count/4)
SELECT score FROM mytable ORDER BY score DESC LIMIT 500, 1; -- floor(count/2)
SELECT score FROM mytable ORDER BY score DESC LIMIT 750, 1; -- floor(count*3/2)
SELECT score FROM mytable ORDER BY score DESC LIMIT 999, 1; -- count-1
0
 
m3housingAuthor Commented:
Thanks guys - both answers were v helpful so I have split the points - I think the CF approach is better for my needs though. Thanks for the quick reply!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.