MySQL Forums
Forum List  »  Newbie

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:
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.

Options: ReplyQuote

Written By
How do I get the median instead of average?
June 28, 2010 02:48AM

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.