The way your query is written is possible and works. However, it's not the most versatile. I would suggest to you, you join tables using INNER/OUTER/FULL JOIN. I rewrote your query and changed it that way, that it solved your problems:SELECT RN.BEGIN_DATE, M.MARKET_CODE, AVG(E.RATE_AMOUNT)

FROM RESERVATION_DAILY_ELEMENT_NAME N

INNER JOIN RESERVATION_DAILY_ELEMENTS E ON N.RESV_DAILY_EL_SEQ = E.RESV_DAILY_EL_SEQ

INNER JOIN RESERVATION_NAME RN ON RN.RESV_NAME_ID = N.RESV_NAME_ID

RIGHT OUTER JOIN MARKET_CODES_TEMPLATE M ON E.MARKET_CODE = M.MARKET_CODE

WHERE TRUNC(RN.BEGIN_DATE) BETWEEN TRUNC(PMS_P.BUSINESS_DATE) AND TRUNC(PMS_P.BUSINESS_DATE +1)

GROUP BY RN.BEGIN_DATE, E.MARKET_CODE

ORDER BY RN.BEGIN_DATEIf there are still bugs, please post the DDL as described in the link below.