Hi I have a requirment as follows
please find the test data scripts attached.
CREATE TABLE "DSGCIS"."TEST1"
("ID_NBR" NUMBER,
"CODEIDA" VARCHAR2(20 BYTE),
"CODEIDB" VARCHAR2(20 BYTE),
"CODEIDC" VARCHAR2(20 BYTE))
SET DEFINE OFF;
Insert into TEST1 (CODEIDA,CODEIDB,CODEIDC,ID_NBR) values ('Y','N','Y',1);
Insert into TEST1 (CODEIDA,CODEIDB,CODEIDC,ID_NBR) values ('Y','Y','N',2);
CREATE TABLE "TEST2" (
"ID_NBR" NUMBER,
"NAME" VARCHAR2(20 BYTE),
"LOC" VARCHAR2(20 BYTE)
);
SET DEFINE OFF;
Insert into TEST2 (ID_NBR,NAME,LOC) values (1,'TEST','NEWYORK');
Insert into TEST2 (ID_NBR,NAME,LOC) values (1,'TEST1','NEWYORK');
Insert into TEST2 (ID_NBR,NAME,LOC) values (2,'TEST2','DENMARK');
When i ran my query as below i am getting the output
select TEST1.ID_NBR,
TEST1.CODEIDA,
TEST1.CODEIDB,
TEST1.CODEIDC,
TEST2.ID_NBR,
TEST2.NAME,
TEST2.LOC,
COUNT(
CASE
WHEN TEST1.CODEIDA = 'Y' THEN
1
END
)
OVER(PARTITION BY TEST1.ID_NBR) CODE_A_CNT ,
COUNT(
CASE
WHEN TEST1.CODEIDB = 'Y' THEN
1
END
)
OVER(PARTITION BY TEST1.ID_NBR) CODE_B_CNT ,
COUNT(
CASE
WHEN TEST1.CODEIDC = 'Y' THEN
1
END
)
OVER(PARTITION BY TEST1.ID_NBR) CODE_C_CNT
FROM TEST1,TEST2
WHERE TEST1.ID_NBR=TEST2.ID_NBR
but expected out put is to show the code_A_cnt and code_c_cnt should show 1 because it is the same ID number and two clients/names are involved.
can you please advise.
thanks so much