Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3958
  • Last Modified:

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
0
m3housing
Asked:
m3housing
2 Solutions
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now