MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » January 2014

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



fixed

Tom,
Please try the following statement;
With TOTAL_RCDS As (SelectCount(1) As CNTFromQTEMP.T),CTE As (SelectC1,Row_Number() Over() As RNFromQTEMP.T),CTE2 As (SelectA.C1,B.C1 As C1NEXT,A.RN,C.CNTFromCTE AJoinCTE BOnA.RN + 1 = B.RNCross JoinTOTAL_RCDS C)SelectCase When MOD(CNT, 2) = 0 Then (C1 + C1NEXT)/2 Else C1 End As MEDIANVALUEFromCTE2Where RN = (CNT + 1) / 2;

Jim
Date: Mon, 6 Jan 2014 15:16:48 -0800
Subject: Re: Anyone get this sample to work for calculating median?
From: tom.stieger@xxxxxxxxx
To: midrange-l@xxxxxxxxxxxx

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.


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






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact