#3 statistical summary - median is average

[Calvin Martini]

In the statistical summary report under the "d. Actuvity"
section, the "Median" value is actually just a simple
average. Try to obtain a true statistical median value.

[Calvin Martini]

Logged In: YES
user_id=841355

Here's a possible partial solution from
http://lists.mysql.com/mysql/155528

#### To get the median of the values in a column:

DROP TABLE IF EXISTS medians;

CREATE TEMPORARY TABLE medians
SELECT x.val medians
FROM data x, data y
GROUP BY x.val
HAVING SUM(y.val <= x.val) >= COUNT(*)/2
AND SUM(y.val >= x.val) >= COUNT(*)/2;

SELECT AVG(medians) AS median FROM medians;

#### Output using my sample data given above
+--------+
| median |
+--------+
| 5.0000 |
+--------+

#### To get the median of the values in a column for each
value in
#### another column:

DROP TABLE IF EXISTS medians;

CREATE TEMPORARY TABLE medians
SELECT x.name, x.val medians
FROM data x, data y
WHERE x.name=y.name
GROUP BY x.name, x.val
HAVING SUM(y.val <= x.val) >= COUNT(*)/2
AND SUM(y.val >= x.val) >= COUNT(*)/2;

SELECT name, AVG(medians) AS median FROM medians GROUP
BY name;

#### Output using my sample data given above
+------+--------+
| name | median |
+------+--------+
| a | 7.0000 |
| b | 3.5000 |
+------+--------+

[Jeffrey Hulten]

Logged In: YES
user_id=1890

There are two possibilities here. One, get the actual
median. Two, relabel to field to 'mean' and be done with it.
Is the median really meaningful in this case? I have not
poked into this piece yet, so I do not know.