Subject: Re: Anyone get this sample to work for calculating median? From: Tom Stieger Date: Mon, 6 Jan 2014 15:16:48 -0800 List-archive: List-help: List-id: Midrange Systems Technical Discussion List-post: List-subscribe: , List-unsubscribe: ,

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.