PostgreSQL error: “subquery uses ungrouped column”

group bypostgresqlsubquery

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?

Best Answer

You could use a subselect:

SELECT q.date,
       q.total_message,
       q.room_id,
       (SELECT seller_id
        FROM chat_room cr
        WHERE cr.id = q.room_id
        LIMIT 1) seller_id,
       (SELECT count(1)
        FROM "order" o
        WHERE o.room_id = q.room_id) total_order,
       (SELECT sum(total_amount)
        FROM "order" o
        WHERE o.room_id = q.room_id
        and DATE(o.created_at) = q.date) total_order_amount
FROM (SELECT DATE(cm.created_at) as "date", 
             COUNT(1) as total_message,
             room_id,
      FROM chat_message cm
      GROUP BY DATE(cm.created_at), cm.room_id) AS q
ORDER BY q.date;
Related Question