Hi I have a requirement in my applciaton
eg.
CREATE TABLE "T1"
( "ID" NUMBER,
"TESTCOL" VARCHAR2(20 BYTE),
"COLUMN1" VARCHAR2(20 BYTE),
"COLUMN2" VARCHAR2(20 BYTE),
"COLUMN3" VARCHAR2(20 BYTE)
) ;
Insert into T1 (ID,TESTCOL,COLUMN1,COLUMN2,COLUMN3) values (1,'N','TEST1','CLERK','10');
Insert into T1 (ID,TESTCOL,COLUMN1,COLUMN2,COLUMN3) values (2,'Y','TEST3','MANAGER','10');
Insert into T1 (ID,TESTCOL,COLUMN1,COLUMN2,COLUMN3) values (3,'Y','TEST3','TEACHER','10');
Insert into T1 (ID,TESTCOL,COLUMN1,COLUMN2,COLUMN3) values (4,'Y','test4','IT','20');
Insert into T1 (ID,TESTCOL,COLUMN1,COLUMN2,COLUMN3) values (5,'N','test5','test','20');
commit;
SELECT
ID,TESTCOL,
DECODE(TESTCOL,'Y',COUNT(TESTCOL) OVER (PARTITION BY COLUMN3),'0')CNT,
COLUMN1,
COLUMN2
FROM T1;
showing the result as belo but i want the cnt to be based on testcol value i.e., if Y then cnt else 0
so in this case cnt for 10 should be 2 and for 20 only 1
because the two other are N which is not to be counted
i cannot use group by as i am using so many other columns in my original query.
please advise
thanks very much
