I’m making a PSQL query and I’m encountering an error which is:
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.
Any idea how to make my column count working ?
EDIT: To clarify one thing, the column target_id is a foreign key to a shop’s id