create table TEST_CASETBL(ID,NUM,TYPE,CREATED_BY,CREATED_DT) as select
1,1-1,CO,ABC,sysdate-10 from DUAL union all select
2,1-1,EO,ABC,sysdate-10 from DUAL union all select
3,1-2,CO,ABC,sysdate-10 from DUAL union all select
4,1-2,UPD,ABC,sysdate-10 from DUAL union all select
5,1-3,CO,XYZ,sysdate-10 from DUAL union all select
6,1-3,EO,XYZ,sysdate-10 from DUAL union all select
7,1-4,CO,XYZ,sysdate-10 from DUAL union all select
8,1-4,NT,XYZ,sysdate-10 from DUAL union all select
9,1-5,EO,XYZ,sysdate-10 from DUAL union all select
10,1-5,NT,XYZ,sysdate-10 from DUAL;
I would like to calculate count of NUM,count of different values in 'TYPE' column based on CREATED_BY Column
Expected result:
CREATED_BY Count(NUM) Count(CO) Count(EO) Count(NT) Count(UPD)
ABC 2(which means 1-1,1-2) 2 1 0 1
XYZ 3(which means 1-3,1-4,1-5) 2 2 2 0
Please provide your suggestions to solve form SQL Query
Thank you