On Wednesday, December 3, 2003, at 06:27 PM, Robert Citek wrote:
How can I calculate the mean/median/mode from a set of data using SQL?
After a bit of googling, I found this link: http://mysql.progen.com.tr/doc/en/Group_by_functions.html and a few answers in the comments towards the bottom.
Below I've included a sample table and the solution I used to calculate the median.
Regards, - Robert
-----
DROP TABLE IF EXISTS data;
CREATE TABLE data ( name char(1) default NULL, val int(5) default NULL ) TYPE=MyISAM;
INSERT INTO data VALUES
('a',2), ('a',2), ('a',2), ('a',2), ('a',20),
('b',4), ('b',4), ('b',4), ('b',4), ('b',40);CREATE TEMPORARY TABLE medians SELECT x.name, x.val
FROM data x, data y
WHERE x.name=y.name
GROUP BY x.name, x.val
HAVING ((COUNT(*)-(SUM(SIGN(1-SIGN(y.val-x.val))))) <= floor((COUNT(*) +1)/2)) and
((COUNT(*)-(SUM(SIGN(1+SIGN(y.val-x.val))))) <= floor((COUNT(*) +1)/2));
SELECT name, AVG(val) AS median FROM medians group by name;
#### Output
+------+--------+ | name | median | +------+--------+ | a | 2.0000 | | b | 4.0000 | +------+--------+
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
