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.