From: Robert Citek [mailto:[EMAIL PROTECTED] > Hello all, > > How can I calculate the mean/median/mode from a set of data using SQL? > > Mean seems to exist as the average (avg): > select name, avg(value) from table group by name > > Is there a way to calculate median and mode with a group by clause? > > Median: the value at which 50% of the samples are above and > below that value. > Mode: the most common value
For mode, this should work: SELECT COUNT(value) AS mode FROM table GROUP BY value ORDER BY mode DESC LIMIT 1; As for median, it's sort of a hack, but this may do the trick: SELECT FLOOR(COUNT(value)/2) FROM table; SELECT name, value FROM table ORDER BY value ASC LIMIT <previous result>, 1; Caveat: That'll work if you have an odd # of rows in the table (I have 15 in my test table); I don't know, mathematically, what median should return for a set of data of an even number, actually. The middle two? Or should it pick one? Good luck either way. -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
