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

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.