Re: Anyone get this sample to work for calculating median?

[Tom Stieger <tom.stieger@xxxxxxxxx>]

Unfortunately for DB2 for i we can't use the OVER() clause on COUNT, SUM,
and other aggregate functions, although you shouldn't need that for this.

The following should work on DB2 for i, although I can't test it now;

CREATE TABLE T(c1 INT);
INSERT INTO T VALUES 10, 12, 18, 30, 33, 50;

 with cte as (SELECT c1, ROW_NUMBER() OVER() as rn, count(c1) AS cnt FROM T
ORDER BY c1),
cte2 as (SELECT A.c1, B.c1 as c1next, A.rn, A.cnt from cte A JOIN cte B on
A.rn + 1 = B.rn)

SELECT CASE when MOD(cnt, 2) = 0 then (c1 + c1next)/2 else c1 end as
medianValue FROM cte2
  WHERE rn = (cnt + 1) / 2;


I'm probably missing some edge cases, but my cte2 is basically doing
the LEAD function that is present in DB2 LUW and other RDBMS. Then I
am doing a test for an even or odd count of records to pick the
average of the two middle numbers or the middle number itself.


Hope this helps you get started.


-Tom Stieger

tom.stieger@xxxxxxxxx




On Mon, Jan 6, 2014 at 2:07 PM, Matt Olson <Matt.Olson@xxxxxxxx> wrote:

> Doesn't seem to work on IBM I's DB2 variant.
>
>
> https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/how_to_do_median_in_db253?lang=en
>
> Get syntax error at (
>
> Presumably because of count(1) over()
>
> Not sure what to use for alternative syntax however.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.