转载

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