postgresql ERROR

      SELECT c.* , COALESCE(SUM(ca.quantity), 0) AS acquired_quantity
        FROM coupon_bundle cb
            INNER JOIN coupon_bundle_item cbi ON cbi.coupon_bundle_id=cb.id
            INNER JOIN coupon c ON c.id=cbi.coupon_id
            LEFT JOIN coupon_acquisition ca ON ca.coupon_id=c.id
        WHERE cb.deleted_at IS NULL

ERROR: column "c.id" must appear in the GROUP BY clause or be used in an aggregate function

错误原因：SELECT 中的 sum()函数为aggregate function, 它将合并行计算。在上例中，没有指定这些行按照哪一列进行合并。

解决方案：查询结果集按sum() 函数包含以外的列进行分组。若在一个表中则按照该表的主键分组即可。

     SELECT c.* , COALESCE(SUM(ca.quantity), 0) AS acquired_quantity
        FROM coupon_bundle cb
            INNER JOIN coupon_bundle_item cbi ON cbi.coupon_bundle_id=cb.id
            INNER JOIN coupon c ON c.id=cbi.coupon_id
            LEFT JOIN coupon_acquisition ca ON ca.coupon_id=c.id
        WHERE cb.deleted_at IS NULL
        GROUP BY c.id

 

转载于:https://www.cnblogs.com/ChaserChen/p/4873560.html