MySQL Forums
Forum List  »  Newbie

Re: Calculating Percentiles, Quartiles, Median
Posted by: Ramalingam Chelliah
Date: September 10, 2004 03:01AM

Hi,
I am not sure about finding these values in an unordered
(yet to be sorted) table.
But You can create a new table and copy the sorted contents(selected values )...

Then JOIN that table with itself (using LEFT OUTER JOIN)
ON condition that ID(PRIMARY KEY) differ by one

Say a table Percentile contains

CREATE TABLE
(ID INTEGER NOT NULL AUTO_INCRMENT PRIMARY KEY
Value INTEGER);


Percentile

ID Value
1 1
2 5
3 9
4 6
5 7
6 7
7 4
8 3
9 5
10 2
11 9
12 9


Now create a table NewPercentile

mysql>DROP TABLE IF EXISTS NewPercentile;

CREATE TABLE NewPercentile
(ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)
SELECT Value FROM Percentile
ORDER BY Value;

Store no of records in NewPercentile in a variable, say @count

SELECT @count=COUNT(*) FROM NewPercentile;


SELECT X.ID,Y.ID,X.Value,Y.Value,(X.Value+Y.Value)/2
FROM
NewPercentile as X
LEFT OUTER JOIN
NewPercentile as Y
ON X.ID=Y.ID+1
WHERE
X.ID=(@count/4)+1
OR
X.ID=(@count/2)+1
OR
X.ID=(3*@count/4)+1;

+----+------+-------+-------+---------------------+
| ID | ID | Value | Value | (X.value+Y.value)/2 |
+----+------+-------+-------+---------------------+
| 4 | 3 | 4 | 3 | 3.50 |
| 7 | 6 | 6 | 5 | 5.50 |
| 10 | 9 | 9 | 7 | 8.00 |
+----+------+-------+-------+---------------------+
3 rows in set (0.01 sec)
mysql>

Hope this helps you ...

Regards,
Ram.

P.S:
This works regardless of whether no of rows is ODD/EVEN








We Learn the Most When we have to Invent

Options: ReplyQuote


Subject
Written By
Posted
Re: Calculating Percentiles, Quartiles, Median
September 10, 2004 03:01AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.