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
Last activity: Jul 31, 2025, 03:00 AM