Calcing quartiles using the median

35 pts.
Tags:
IBM i
SQL
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

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    For a start:
    select AVG( TEMP ) from QGPL/WEATHER1
    ...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:
    select COUNT(*) from QGPL/WEATHER1
    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
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following