median and mode in mysql

gistfile1.mkd

From Rick James @ http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

• Mode

    SELECT trimmed_len, COUNT(trimmed_len) AS mode FROM sequence GROUP BY trimmed_len ORDER BY mode DESC LIMIT 1;
  

From Michael Stassen @ http://lists.mysql.com/mysql/155540

• Median

    CREATE TEMPORARY TABLE tmp (n INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, value VARCHAR(99) NOT NULL );
    INSERT INTO tmp (value) SELECT value FROM table ORDER BY 1;
    SELECT @count := COUNT(*) FROM tmp;
    SELECT DISTINCT value FROM tmp WHERE n IN (FLOOR((@count+1)/2), CEIL((@count+1)/2));
    DROP TEMPORARY TABLE tmp;