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.
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.
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;
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
... GROUP BY l.name, s.id;
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_eventsfor the current row’s
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 🙂