I have this query which has sub queries in it. when these sub queries are run separately for different status the result is correct but when statuses are brought together in one query the result is wrong. what could be the reason? select sum(nvl(daysIN1,0)) as daysIN1, sum(nvl(daysOH1,0)) as daysOH1, sum(nvl(daysIN2,0)) as daysIN2, sum(nvl(daysOH2,0)) as daysOH2 from ( select DECODE(STATUS,'Inprocess', count(*)) as daysIN1, DECODE(STATUS,'Onhold', count(*)) as daysOH1 FROM kmlg.v_hms_info_all L where roll_header_id IS NOT NULL AND job_cat_desc is not null AND L.SEC_ID = '0002' AND (TO_DATE(L.ENT_DATE, 'DD-MON-RRRR') <= TO_DATE(:P_AS_ON_DATE,'DD-MON-RRRR') or :P_AS_ON_DATE IS NULL) group by status )days1, ( SELECT DECODE(STATUS,'Inprocess', count(*)) as daysIN2, DECODE(STATUS,'Onhold', count(*)) as daysOH2 FROM KMLG.v_hms_info_all l WHERE 1 = 1 AND TO_DATE (l.ent_date) >= TO_DATE(:P_AS_ON_DATE )- 15 AND roll_header_id IS NOT NULL AND job_cat_desc is not null AND L.SEC_ID = '0002' GROUP BY STATUS )days2 Expected outcome: Current Outcome: daysIN1 = 167 daysIN1 = 668 daysOH1 = 101 daysOH1 = 404 daysIN2 = 102 daysIN2 = 408 daysOH2 = 1 daysOH2 = 4