Re: BUG #18430: syntax error when using aggregate function in where clause of subquery - Mailing list pgsql-bugs

On Fri, 2024-04-12 at 17:14 +0000, PG Bug reporting form wrote:
> SELECT
>     city,
>     (SELECT count(*) FROM delivery WHERE driver_id IN array_agg(driver.id))
> AS deliveries
> FROM driver
> GROUP BY city
> ;
>
> This produces:
>
> ERROR:  syntax error at or near "array_agg"
> LINE 3: ...(SELECT count(*) FROM delivery WHERE driver_id IN array_agg(...

This not a bug, but bad syntax.

Write

  SELECT city,
         (SELECT count(*)
          FROM delivery
          WHERE driver_id = ANY (drivers)) AS deliveries
  FROM (SELECT city,
               array_agg(driver.id) AS drivers
        FROM driver
        GROUP BY city) AS q;

Yours,
Laurenz Albe