SQL & PL/SQL

Hi all

Any help is appreciated

I have a query that will calculate the R_square for Every 3 rows A

When i do the calculation from query for first 3 rows it is calculating correct

Next set of rows is calculating wrong

I checked calculation using Rsquare formula Excel Spread sheet

SELECT a.*,
       REGR_R2 (average, prof) OVER (ORDER BY rn
                                     ROWS BETWEEN CASE WHEN MOD (rn, 3) = 0
                                                            THEN 3
                                                       WHEN rn_desc = 1
                                                            THEN  MOD (rn, 3)
                                                       ELSE  0
                                                  END  PRECEDING
                                           AND CURRENT ROW) AS r_square 
FROM   (select b.*,
               row_number() over (order by b.a) rn,
               row_number() over (order by b.a desc) rn_desc
        from   tableaa b
       ) a
ORDER BY  a.a;

1	A	BEG	END	PROF	AVERAGE	REGION	RN	RN_DESC	R_SQUARE	Excel Formula(
2	1	0	0.1	159	159	1	1	14		
3	2	0.1	0.2	159	168	1	2	13		
4	3	0.2	0.3	179	159	1	3	12	0.25		0.25        =RSQ(E2:E4,D2:D4)
5	4	0.1	0.2	250	300	1	4	11		
6	5	0.2	0.3	320	250	1	5	10		
7	6	0.3	0.4	250	380	1	6	9	0.164520715	0.627906977 ===RSQ(E5:E7,D5:D7)
8	7	0.2	0.3	388	379	2	7	8		
9	8	0.3	0.4	379	388	2	8	7		
10	9	0.4	0.5	388	400	2	9	6	0.218256852	0.006756757 ==RSQ(E8:E10,D8:D10)
11	10	1.5	0.6	499	500	2	10	5		
12	11	0.5	0.6	420	448	2	11	4		
13	12	0.6	0.7	520	530	1	12	3	0.973538061	0.971560719 =RSQ(E11:E13,D11:D13)
14	13	0.7	0.8	540	550	1	13	2		
15	14	0.9	1	560	570	1	14	1	1		1           =RSQ(E13:E15,D13:D15)

When i do seperatly i am getting correct value what excel is showing

select REGR_R2 (average, prof) from tableaa
where a in (1,2,3)

REGR_R2(AVERAGE,PROF)
0.25


select REGR_R2 (average, prof) from tableaa
where a in (4,5,6)

REGR_R2(AVERAGE,PROF)
0.627906976744186

create table TABLEAA
(
  A       NUMBER,
  BEG     NUMBER,
  END     NUMBER,
  PROF    NUMBER,
  AVERAGE NUMBER,
  REGION  NUMBER
)
;

prompt Loading TABLEAA...

insert into TABLEAA (A, BEG, END, PROF, AVERAGE, REGION)
values (1, 0, .1, 159, 159, 1);
insert into TABLEAA (A, BEG, END, PROF, AVERAGE, REGION)
values (2, .1, .2, 159, 168, 1);
insert into TABLEAA (A, BEG, END, PROF, AVERAGE, REGION)
values (3, .2, .3, 179, 159, 1);
insert into TABLEAA (A, BEG, END, PROF, AVERAGE, REGION)
values (4, .1, .2, 250, 300, 1);
insert into TABLEAA (A, BEG, END, PROF, AVERAGE, REGION)
values (5, .2, .3, 320, 250, 1);
insert into TABLEAA (A, BEG, END, PROF, AVERAGE, REGION)
values (6, .3, .4, 250, 380, 1);
insert into TABLEAA (A, BEG, END, PROF, AVERAGE, REGION)
values (7, .2, .3, 388, 379, 2);
insert into TABLEAA (A, BEG, END, PROF, AVERAGE, REGION)
values (8, .3, .4, 379, 388, 2);
insert into TABLEAA (A, BEG, END, PROF, AVERAGE, REGION)
values (9, .4, .5, 388, 400, 2);
insert into TABLEAA (A, BEG, END, PROF, AVERAGE, REGION)
values (10, 1.5, .6, 499, 500, 2);
insert into TABLEAA (A, BEG, END, PROF, AVERAGE, REGION)
values (11, .5, .6, 420, 448, 2);
insert into TABLEAA (A, BEG, END, PROF, AVERAGE, REGION)
values (12, .6, .7, 520, 530, 1);
insert into TABLEAA (A, BEG, END, PROF, AVERAGE, REGION)
values (13, .7, .8, 540, 550, 1);
insert into TABLEAA (A, BEG, END, PROF, AVERAGE, REGION)
values (14, .9, 1, 560, 570, 1);
commit;

Edited by: user1849 on Sep 22, 2009 11:59 AM

Edited by: user1849 on Sep 22, 2009 12:00 PM