Median function in SQL - IBM AS400

This is a discussion on Median function in SQL - IBM AS400 ; Does anyone know of a way to do a median function in DB2/400 SQL?...

1. Median function in SQL

Does anyone know of a way to do a median function in DB2/400 SQL?

2. Re: Median function in SQL

On Aug 23, 2:14*am, Old Fossil Bama wrote:
> Does anyone know of a way to do a median function in DB2/400 SQL?

Would (max(field) + min(field)) /2 be right?

Jonathan

3. Re: Median function in SQL

On Aug 26, 4:11*am, Jonathan Bailey wrote:
> On Aug 23, 2:14*am, Old Fossil Bama wrote:
>
> > Does anyone know of a way to do a median function in DB2/400 SQL?

>
> Would (max(field) + min(field)) /2 be right?
>
> Jonathan

Thanks, Jonathan!

Works great.

4. Re: Median function in SQL

On Aug 26, 2:26*pm, Old Fossil Bama wrote:
> On Aug 26, 4:11*am, Jonathan Bailey wrote:
>
> > On Aug 23, 2:14*am, Old Fossil Bama wrote:

>
> > > Does anyone know of a way to do a median function in DB2/400 SQL?

>
> > Would (max(field) + min(field)) /2 be right?

>
> > Jonathan

>
> Thanks, Jonathan!
>
> Works great.

Hey bama, would AVG(field) have worked??

select avg(field) lib/file

5. Re: Median function in SQL

"Old Fossil Bama" wrote in message
On Aug 26, 4:11 am, Jonathan Bailey wrote:
> On Aug 23, 2:14 am, Old Fossil Bama wrote:
>
> > Does anyone know of a way to do a median function in DB2/400 SQL?

>
> Would (max(field) + min(field)) /2 be right?
>
> Jonathan

Thanks, Jonathan!

Works great.

Uh...

I think median is defined as the value x where half of the records are
less x and half of the records are more than x.

If the population is evenly distributed between the max and min
values then the median will be equal to the mean which is the simple
algebraic average you calculated. However, if there are many records
near one value and a few at some higher or lower extremes then the
median will be far from the mean.

see: http://www.purplemath.com/modules/meanmode.htm

Mike Sicilian

6. Re: Median function in SQL

Old Fossil Bama wrote:
> Jonathan Bailey wrote:
>> Old Fossil Bama wrote:
>>
>>> Does anyone know of a way to do a median function in DB2/400 SQL?

>>
>> Would (max(field) + min(field)) /2 be right?

>
> Works great.

I think "works" needs to be examined closely, if the intent was
really to satisfy the original request.

If there is a requirement to get the median versus a mean of the end
points, I would recommend looking for a /real/ solution. That is, the
average of the end points will atypically equate with the median. For
example the list of eleven values and the results for median, mean, and
average of the endpoints:

12, 24, 36, 36, 38, 44, 50, 55, 66, 87, 102

Median of the above eleven data points is forty four; i.e. there are
five values less than 44, and five values more than 44.

Mean or average of the above eleven data points is fifty; i.e. the
values added is 550, divided by the number of values 11, is 50:

( 12 +24 +36 +36 +38 +44 +50 +55 +66 +87 +102 ) / 11 =>
550 / 11 => 50

The average of the end points, which is not the median, is fifty
seven; i.e. the minimum value of 12 plus the maximum value of 102 is
114, and 114 divided by 2 is 57:

(min()+max())/2 => 114/2 => 57

I seem to recall there is a DB2 solution documented using a FUNCTION;
probably an external function. There are also ROW_NUMBER solutions for
which an ordered list of values enables finding the middle count value
from which its value can be used as median, or average of the two
midpoint values for an even number of rows. As shown above, the median
of the values in an ordered list is trivial; at least for an odd number
of values -- the definition of /median/ is specific to the application,
for how to handle an even number of values. A search on the tokens
median+db2+cookbook+row_number should find an example of the latter.

Regards, Chuck

7. Re: Median function in SQL

> >>> Does anyone know of a way to do a median function in DB2/400 SQL?
>
> >> Would (max(field) + min(field)) /2 be right?

>
> > Works great.

>
> * *I think "works" needs to be examined closely, if the intent was
> really to satisfy the original request.
>
> * *If there is a requirement to get the median versus a mean of the end
> points, I would recommend looking for a /real/ solution. *That is, the
> average of the end points will atypically equate with the median. *For
> example the list of eleven values and the results for median, mean, and
> average of the endpoints:
>
> * *12, 24, 36, 36, 38, 44, 50, 55, 66, 87, 102
>
> * *Median of the above eleven data points is forty four; i.e. there are
> five values less than 44, and five values more than 44.
>
> * *Mean or average of the above eleven data points is fifty; i.e. the
> values added is 550, divided by the number of values 11, is 50:
>
> * * *( 12 +24 +36 +36 +38 +44 +50 +55 +66 +87 +102 ) / 11 *=>
> * * * 550 / 11 => 50
>
> * *The average of the end points, which is not the median, is fifty
> seven; i.e. the minimum value of 12 plus the maximum value of 102 is
> 114, and 114 divided by 2 is 57:
>
> * *(min()+max())/2 => 114/2 => 57
>
> * *I seem to recall there is a DB2 solution documented using a FUNCTION;
> probably an external function. *There are also ROW_NUMBER solutions for
> which an ordered list of values enables finding the middle count value
> from which its value can be used as median, or average of the two
> midpoint values for an even number of rows. *As shown above, the median
> of the values in an ordered list is trivial; at least for an odd number
> of values -- the definition of /median/ is specific to the application,
> for how to handle an even number of values. *A search on the tokens
> median+db2+cookbook+row_number should find an example of the latter.
>
> Regards, Chuck

Oops, you are absolutely right, Chuck. I should have double checked by
original result. I had a whole bunch of single digit numbers plus a
handful of triple digits in the file and assumed the result was ok,
but it wasn't.

Will have to keep looking...

8. Re: Median function in SQL

On Aug 26, 9:57*pm, Old Fossil Bama wrote:
> > >>> Does anyone know of a way to do a median function in DB2/400 SQL?

>
> > >> Would (max(field) + min(field)) /2 be right?

>
> > > Works great.

>
> > * *I think "works" needs to be examined closely, if the intent was
> > really to satisfy the original request.

>
> > * *If there is a requirement to get the median versus a mean of theend
> > points, I would recommend looking for a /real/ solution. *That is, the
> > average of the end points will atypically equate with the median. *For
> > example the list of eleven values and the results for median, mean, and
> > average of the endpoints:

>
> > * *12, 24, 36, 36, 38, 44, 50, 55, 66, 87, 102

>
> > * *Median of the above eleven data points is forty four; i.e. thereare
> > five values less than 44, and five values more than 44.

>
> > * *Mean or average of the above eleven data points is fifty; i.e. the
> > values added is 550, divided by the number of values 11, is 50:

>
> > * * *( 12 +24 +36 +36 +38 +44 +50 +55 +66 +87 +102 ) / 11 *=>
> > * * * 550 / 11 => 50

>
> > * *The average of the end points, which is not the median, is fifty
> > seven; i.e. the minimum value of 12 plus the maximum value of 102 is
> > 114, and 114 divided by 2 is 57:

>
> > * *(min()+max())/2 => 114/2 => 57

>
> > * *I seem to recall there is a DB2 solution documented using a FUNCTION;
> > probably an external function. *There are also ROW_NUMBER solutions for
> > which an ordered list of values enables finding the middle count value
> > from which its value can be used as median, or average of the two
> > midpoint values for an even number of rows. *As shown above, the median
> > of the values in an ordered list is trivial; at least for an odd number
> > of values -- the definition of /median/ is specific to the application,
> > for how to handle an even number of values. *A search on the tokens
> > median+db2+cookbook+row_number should find an example of the latter.

>
> > Regards, Chuck

>
> Oops, you are absolutely right, Chuck. I should have double checked by
> original result. I had a whole bunch of single digit numbers plus a
> handful of triple digits in the file and assumed the result was ok,
> but it wasn't.
>
> Will have to keep looking...- Hide quoted text -
>
> - Show quoted text -

Looks like I have forgotten some of my basic maths...
Good job everyone else is on the ball.

Jonathan