MySQL Forums :: Newbie :: How do I get the median instead of average?


Advanced Search

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.

Options: ReplyQuote


Subject Written By Posted
How do I get the median instead of average? Sarah B 06/28/2010 02:48AM
Re: How do I get the median instead of average? Rick James 06/29/2010 07:16PM
Re: How do I get the median instead of average? laptop alias 06/30/2010 01:40AM
Re: How do I get the median instead of average? Rick James 06/30/2010 08:06PM
Re: How do I get the median instead of average? laptop alias 07/01/2010 04:05AM
Re: How do I get the median instead of average? Amy Bowers 07/01/2010 05:04PM
Re: How do I get the median instead of average? Peter Brawley 07/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.