MIDRANGE dot COM Mailing List Archive

Home » MIDRANGE-L » December 2013

Re: Where are the CORRELATION, VARIANCE, and COVARIANCE functions in IBM i DB2


My apologies, Tom - your reference to LUW was confusing.

These should work - I had thought that what the OP wanted should be capable of being expressed in terms of simpler, existing functions.

Unless you really want the cross join that this will produce - I think!! - you might want to use true JOIN syntax instead of the table list with no WHERE clause to define the JOINs.

Nicely done - thanks!


On 12/3/2013 5:55 PM, Tom Stieger wrote:
Can I ask why my code won't work? It is just using standard DB2 for i
aggregate functions to emulate what those standard statistical function do.
I don't know statistics very well, but a brief look online should me that
my calculations seem to match the standard definitions. DISCLAIMER: EVEN IF

On your point about looking at DB2 LUW documentation, I agree. Usually
only two things can come of it: confusion as to why something doesn't work
or anger as to why that isn't in DB2 for i yet!


On Tue, Dec 3, 2013 at 2:58 PM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>wrote:

This will not work on IBM i, which is the flavor of DB2 the OP is asking

Documentation for DB2 LUW (Linux-Unix-Windows) is generally not
something I recommend to anyone running on IBM i. It will probably end
up just confusing a person.


On 12/3/2013 3:08 PM, Tom Stieger wrote:
Based on the links for DB2 LUW, the following should get you what you
avgcte as (SELECT AVG(exp1) as avgexp1, AVG(exp2) as avgexp2 FROM
covarcte as (SELECT AVG((exp1 - avgexp1)*(exp2-avgexp2)) as covarvalue
table1, avgcte),
corrcte as (SELECT covarvalue/(STDDEV(exp1)*STDDEV(exp2)) as corrvalue
table1, covarcte)

SELECT * from avgcte, covarcte, corrcte

I can't test this out right now, but it looks like it might work to me.

-Tom Stieger
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