Sample Header Ad - 728x90

PostgreSQL error: "subquery uses ungrouped column"

0 votes
1 answer
2581 views
I have a query with subqueries that use only elements from the GROUP BY clause:
SELECT 
		DATE(cm.created_at) as "date", 
		COUNT(1) as total_message,
		room_id,
		(SELECT seller_id FROM chat_room cr WHERE cr.id = cm.room_id LIMIT 1) seller_id,
		(SELECT count(1) FROM "order" o WHERE o.room_id = cm.room_id) total_order,
		(SELECT sum(total_amount) FROM "order" o WHERE o.room_id = cm.room_id and DATE(o.created_at) = date(cm.created_at) )  total_order_amount
	FROM chat_message cm
	GROUP BY DATE(cm.created_at), cm.room_id
	ORDER BY DATE(cm.created_at)
Error: ERROR: subquery uses ungrouped column "cm.created_at" from outer query This part of the query causes the error: (SELECT sum(total_amount) FROM "order" o WHERE o.room_id = cm.room_id and DATE(o.created_at) = date(cm.created_at) ) total_order_amount How to solve this?
Asked by yozawiratama (111 rep)
Apr 15, 2020, 03:43 AM
Last activity: Jul 31, 2025, 03:00 AM