ERROR: subquery uses ungrouped column “” from outer query

All we need is an easy explanation of the problem, so here it is.

I’m encountering an error for the below query:

ERROR:  subquery uses ungrouped column "" from outer query
LINE 8:             WHERE target_id = AND type = 'started_d...

My query is:

SELECT AS "City", 
        COUNT(shops) AS "Shops",
        CAST(AVG(shops.rating_cache) AS decimal(10, 2)) AS "Rating",
        SUM(shops.product_count_cache) AS "Products",
            SELECT COUNT(*)
            FROM customer_events
            WHERE target_id = AND type = 'started_directions'
        ) AS "Visites"
FROM shops
LEFT JOIN localities ON = shops.locality_id
WHERE shops.locality_id
IN (
    SELECT cast(unnest as uuid) 
     unnest(string_to_array('9c57227a-8f4e-44e0-a3a8-1439c25bf2e5,8f285bca-baec-442e-8a21-e067b75d8f13', ','))
) AND shops.onboarding_status = 'ready'


First four selected and calculated columns are working but the 5th which counts the number of customer_events for the current row’s doesn’t work.

The column target_id is a foreign key to

Any idea how to make my column count work?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The correlated subquery to get the count for "Visites" uses the un-aggregated column – just like the error message is trying to tell you.

You need to work with an aggregated value instead. This fixes the error:

     , count(shops) AS "Shops"
     , avg(s.rating_cache)::decimal(10, 2) AS "Rating"
     , sum(s.product_count_cache) AS "Products"
     , ( SELECT count(*)
         FROM   customer_events c
         WHERE  c.target_id = min(              -- !!!
         AND    c.type = 'started_directions'
       ) AS "Visites"
FROM   shops s
LEFT   JOIN localities l ON = s.locality_id
WHERE  s.locality_id = ANY (string_to_array('9c57227a-8f4e-44e0-a3a8-1439c25bf2e5,8f285bca-baec-442e-8a21-e067b75d8f13', ',')::uuid[])
AND    s.onboarding_status = 'ready'

I use min(, the smallest value in the group after grouping by localities. Adapt to your requirements. Maybe you want a separate group for each different


If happens to be always the same for the same either variant ends up doing the same. (I would wonder about the functional dependency in the 2nd case, though …)

Your original query would work if the column name was the PK of the table localities, because the PK covers the whole row. But not otherwise. See:

One more suspicious detail. You explained:

which counts the number of customer_events for the current row’s

But the query counts for, not for One or the other is wrong.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply