I have a table with below data
A_CD S_ID E_CD S_E S_NE E_IND
---------- ---------- ---------- ----- ----- -
100 1 3 A J Y
100 1 3 B A N
100 1 3 A D
200 2 3 A C Y
200 2 3 D C
300 3 3 B N Y
300 3 3 N N
I need to write a sql query
to get count of distinct S_E,S_NE group by a_cd and (S_E, S_NE) as letter codes with below conditions
1) take the value of S_NE when (E_CD = 3 AND E_IND = N)
2) take the value of S_E when E_CD = 3 AND E_IND = Y
3) take the value of S_NE when E_CD = 3 AND E_IND is null AND DISPLAY E_IND as N
4) take the value of S_E when E_CD = 3 AND E_IND is null AND DISPLAY E_IND as Y
The output should be like below. L_C is count of S_E,S_NE
A_CD L_C COUNT E_IND
---- --- ----- -----
100 A 3 Y
100 D 1 N
200 A 1 Y
200 D 1 Y
200 C 1 N
300 B 1 Y
300 N 1 Y
300 N 1 N
Sample data and table creation is below
create table Mate2(a_cd number(5),S_ID NUMBER, E_CD number(10), S_E varchar2(5), S_NE varchar2(5), E_IND CHAR(1 byte));
insert into Mate2 values (100,1,3,'A','J','Y');
insert into Mate2 values (100,1,3,'B','A','N');
insert into Mate2(a_cd,S_ID,E_CD,S_E,S_NE) values (100,1,3,'A','D');
insert into Mate2 values (200,2,3,'A','C','Y');
insert into Mate2(a_cd,S_ID,E_CD,S_E,S_NE) values (200,2,3,'D','C');
insert into Mate2 values (300,3,3,'B','N','Y');
insert into Mate2(a_cd,S_ID,E_CD,S_E,S_NE) values (300,3,3,'N','N');
commit;