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 PICKUPS,

       /* 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

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?

If I remove the two SUM() lines and the GROUP BY it works perfectly.

I am using Firebird 2.5 32-bit on Windows 7 64-bit.