mysql - 对 sum 函数使用 group 函数无效

Question

My SQL 查询返回错误#1111 - 对结果中的行求和时组函数的无效使用

此查询工作正常并返回许多行。

SELECT credit.Amount - SUM( COALESCE( creditreceived.Amount, 0 ) ) AS 
AmountReceivable FROM credit LEFT JOIN creditreceived ON credit.ID = 
creditreceived.CreditID WHERE credit.CompanyID =  '1' GROUP BY credit.id

结果图像: http://oi65.tinypic.com/qplyli.jpg

我想对这些行求和，为此我正在使用此查询

SELECT SUM( credit.Amount - SUM( COALESCE( creditreceived.Amount, 0 ) ) ) AS
AmountReceivable FROM credit LEFT JOIN creditreceived ON credit.ID = 
creditreceived.CreditID WHERE credit.CompanyID =  '1' GROUP BY credit.id

这将返回错误#1111 - 组函数的使用无效

Answer 1

它认为问题不在于分组依据。尝试一下

SUM(credit.Amount - COALESCE(creditreceived.Amount, 0 ) ) AS..