Calcing quartiles using the median
I'm using this SQL to determine the median of a group of temperatures...
select x.TEMP from QGPL/WEATHER1 x, QGPL/WEATHER1 y
group by x.TEMP
having sum(case when y.TEMP <= x.TEMP then 1 else 0 end)
>=(count(*)+1)/2 and
sum(case when y.TEMP >= x.TEMP then 1 else 0 end)>= count(*)/2 + 1
If I want the 1st quartile, why couldn't I use the same code but insert a WHERE statement that selects only those values < the value returned from the first SQL? If I want the 3rd quartile, select only those values > value returned from first SQL?
If so, how do I modify the statement to include that WHERE statement?
Software/Hardware used:
IBM i
Software/Hardware used:
IBM i
ASKED:
February 24, 2012 11:04 PM
UPDATED:
March 17, 2012 1:22 AM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
For a start:
…should give you the average (the ‘mean’).
But quartiles should be more related to “median” than “mean”. The “median” of a frequency distribution is the 2nd quartile. The median is just the point where there are as many above as below.
The median can be determined essentially just by counting the ordered data.
Once you have the median, you have the lower half and the upper half of the ordered values. The 1st quartile is just the ‘median’ of the lower half. And the 3rd quartile is then the ‘median’ of the upper half.
In general, the overall count is just:
The median row would be row number COUNT(*)/2 of the ordered set. And the 1st quartile would be row number COUNT(*)/4. (Rounding might be useful for both.)
What version of DB2 are you running. (The version of the OS will be the same for IBM i.)
Tom