MySQL Forums

How do I get the median instead of average?
Posted by: Sarah B
Date: June 28, 2010 02:48AM

Hello all,

I hope you can help me. I'm stuck with a median problem.
My aim was to calculate the median instead of the average.
Well, all I could find out was calculating the average.

That's the query so far:
SELECT
ROUND(SUM((Unix_Timestamp(te.end)-Unix_Timestamp(te.start))/3600)/count(distinct t.Col1),3) AS Durchschnitt
FROM Table1 t
INNER JOIN Table2 te ON t.Col1 = te.Col2
WHERE Col3 > 0

Now I thought I could solve the problem by using IF and LIMIT. It was the first thought coming up to my mind and it was the only one.
IF ((LIMIT(count((distinct t.COL1)+1)/2),(count((distinct t.COl1)+1)/2),
(count((distinct t.COL1)+1)/2),(count((distinct t.COl1)+1)/2)),
(LIMIT(count((distinct t.COL1)+1)/2),(count((distinct t.COl1)+1)/2),
(count((distinct t.COL1)+1)/2),(count((distinct t.COl1)+1)/2)),
(LIMIT((count(1/2((distinct t.COL1)+1)/2)+(distinct t.COL1)+1)/2)+1),(count(1/2((distinct t.COL1)+1)/2)+(distinct t.COL1)+1)/2)+1)),
((count(1/2((distinct t.COL1)+1)/2)+(distinct t.COL1)+1)/2)+1),(count(1/2((distinct t.COL1)+1)/2)+(distinct t.COL1)+1)/2)+1)) )

All I got is a colum with 1000 or 1001 entries and two columns with a start and end time.
I summed up the two times to get the hours of the entries.
The median is very important to not get the max and min times. If I just calculate the average the counting includes all times which I don't want.

Can anyone give me a hint or help me solving my problem?
Your help is much appreciated and I like to thank you in advance.

Subject
Written By
Posted
How do I get the median instead of average?
June 28, 2010 02:48AM
June 29, 2010 07:16PM
June 30, 2010 01:40AM
June 30, 2010 08:06PM
July 01, 2010 04:05AM
July 01, 2010 05:04PM
July 01, 2010 06:11PM

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.