select count(1) fromfrom( select id, CASE when a >= 0 and a <= 30 then 'one' when a >= 31 and a <= 60 then 'two' when a >= 61 and a <= 90 then 'three' else 'NO' END FROM tabel_1 t Where ( ( TO_CHAR(t.aDate, 'YYYYMMDD') BETWEEN TO_CHAR(pFrom_Date, 'YYYYMMDD') AND TO_CHAR(pTo_Date, 'YYYYMMDD') //cond1 ) OR ( TO_CHAR(t.bDate, 'YYYYMMDD') BETWEEN TO_CHAR(pFrom_Date, 'YYYYMMDD') AND TO_CHAR(pTo_Date, 'YYYYMMDD') //cond2 )OR ( TO_CHAR(t.bDate, 'YYYYMMDD') BETWEEN TO_CHAR(pFrom_Date, 'YYYYMMDD') AND TO_CHAR(pTo_Date, 'YYYYMMDD') //cond3 ) AND ( p_Type = 'Admin' AND t.ID > 0 //condA )OR < 0 ( SELECT COUNT(1) FROM tab_2 t2 WHERE t2.ID = USER_ID AND t.ID = t2.ID condB ) ) )