ERROR: subquery uses ungrouped column “shops.id” 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 "shops.id" from outer query
LINE 8:             WHERE target_id = shops.id AND type = 'started_d...

My query is:

SELECT  localities.name 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 = shops.id AND type = 'started_directions'
        ) AS "Visites"
FROM shops
LEFT JOIN localities ON localities.id = shops.locality_id
WHERE shops.locality_id
IN (
    SELECT cast(unnest as uuid) 
    FROM
     unnest(string_to_array('9c57227a-8f4e-44e0-a3a8-1439c25bf2e5,8f285bca-baec-442e-8a21-e067b75d8f13', ','))
) AND shops.onboarding_status = 'ready'

GROUP BY localities.name

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

The column target_id is a foreign key to shops.id.

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 shops.id – just like the error message is trying to tell you.

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

SELECT l.name AS "City"
     , 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(s.id)              -- !!!
         AND    c.type = 'started_directions'
       ) AS "Visites"
FROM   shops s
LEFT   JOIN localities l ON l.id = 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'
GROUP  BY l.name; 

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

...
GROUP  BY l.name, s.id;

If shops.id happens to be always the same for the same localities.name 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 localities.id

But the query counts for shops.id, not for localities.id. 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 stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply