07.05.2015 20:23, sboyd...@gmail.com wrote: > The following query: > > > SELECT PB_LOAD_TYPE, > > /* If already picked up use the pick up date. > > If already delivered us the delivered date. > > If appointment date present, use it. > > Otherwise, use today's date */ > > IIF(OPS_ONBOARD_TIME IS NOT NULL, > > CAST(OPS_ONBOARD_TIME AS DATE), > > IIF(OPS_DELIVERED_TIME IS NOT NULL, > > CAST(OPS_DELIVERED_TIME AS DATE), > > COALESCE( CAST(OPS_APPT_LOW AS DATE), CAST('TODAY' AS > DATE)))) AS APPT_DATE, > > /* Get the region ID */ > > COALESCE((SELECT FIRST 1 CTRR_REGION_ID > > FROM CT_REGION_RANGES > > WHERE CTRR_LOW_ZIP <= OPS_ZIP AND > > CTRR_HIGH_ZIP >= OPS_ZIP > > ORDER BY CTRR_REGION_ID), 'N/A') AS REGION_ID, > > /* Count pick ups */ > > SUM(IIF(OPS_TYPE = 'P', 1, 0)) AS P ICKUPS, > > /* Count deliveries */ > > SUM(IIF(OPS_TYPE = 'P', 0, 1)) AS DELIVERIES > > FROM OPS_STOP_REC > > LEFT JOIN OPS_HEADER ON PB_ID = OPS_ORDER_ID > > WHERE PB_TYPE = 'O' AND > > PB_DT_ENT >= :PB_DT_ENT > > GROUP BY APPT_DATE, PB_LOAD_TYPE, REGION_ID > > ORDER BY APPT_DATE, PB_LOAD_TYPE, REGION_ID > > > throws this error: > > Invalid expression in the select list (not contained in either an > aggregate function or the GROUP BY clause) > > There is nothing wrong with the query that I can see. What am I missing?
You cannot GROUP BY subselect, you should instead GROUP BY OPS_ZIP (its link with the parent query). Dmitry