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

Would this be of any help?

http://www.udel.edu/evelyn/SQL-Class3/SQL3_Stat.html

DR2



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jim It
Sent: Monday, January 6, 2014 7:01 PM
To: Midrange Systems Technical Discussion
Subject: RE: Anyone get this sample to work for calculating median?

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/en
try/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.

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