Problem with built-in CORR function

From: Krzysiek299 <krzysztof.iwanicki_at_gmail.com>
Date: 16 Apr 2006 05:34:51 -0700
Message-ID: <1145190891.216455.171460@g10g2000cwb.googlegroups.com>

Hello.
I want to calculate the coefficient of correlation between x with id=1 and x with id=2. mytable is looking something like this: x | id

---

2 | 1
5 | 2
2 | 1
3 | 2
3 | 1
4 | 2

I tried:
SELECT CORR(

     (SELECT x FROM mytable WHERE mytable.id=1),
     (SELECT x FROM mytable WHERE mytable.id=2))

FROM mytable; //or FROM DUAL but it isn't working too but I get an error that substatement returns more than one row (but CORR function requires more than one row)

and this isn't working too (it calculates corr but it has always value 0)
SELECT CORR(x1,x2) FROM
(SELECT x AS x1 FROM mytable WHERE id=1),
(SELECT x AS x2 FROM mytable WHERE id=2);

Please help me! How to calculate corr between x with id=1 and x with id=2? Received on Sun Apr 16 2006 - 07:34:51 CDT