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?...

+ Reply to Thread
Results 1 to 8 of 8

Thread: Median function in 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
    news:0b8668c9-4f9b-4ead-b610-157bba9daf00@l33g2000pri.googlegroups.com...
    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

+ Reply to Thread