从sql查询中的两列中减去值

Question

有两列，我想减去它们。我正在尝试对 [payment] 列求和，然后从 [total_bill] 列中减去它。下面是我的代码，它给了我错误，它无法对包含聚合或子查询的表达式执行聚合函数。

 SELECT SUM((bill_record.total_bill)-SUM(invoice_payments.payment)) AS [LEFT AMOUNT]

原文由 yaseen enterprises 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

您需要使用 group by，但您可能只想将付款相加并从单个总帐单中减去这些付款。

 SELECT bill_record.total_bill - SUM(invoice_payments.payment) AS [LEFT AMOUNT]
FROM bill_record INNER JOIN invoice_payments ON bill_record.PKColumn = invoice_payments.FKColumn
WHERE bill_record.PKColumn = @billId
GROUP BY bill_record.total_bill

---

请注意，此处的 group by 有效，因为您也在 WHERE 子句中进行过滤。如果您想获得多个账单的结果，您还可以按唯一的账单标识符分组，并在 SELECT 中返回它。

 SELECT bill_record.PKColumn AS BillId, bill_record.total_bill - SUM(invoice_payments.payment) AS [LEFT AMOUNT]
FROM bill_record INNER JOIN invoice_payments ON bill_record.PKColumn = invoice_payments.FKColumn
GROUP BY bill_record.PKColumn, bill_record.total_bill

原文由 Igor 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

当您使用 SUM 聚合函数时，您必须正确使用 group by 子句

并且选择列的数量必须在 group by 子句上

SELECT SUM((bill_record.total_bill)-SUM(invoice_payments.payment)) AS [LEFT AMOUNT]

错误的 of 可以按列的子句编号分组，否则您的查询应该可以工作

原文由 Zaynul Abadin Tuhin 发布，翻译遵循 CC BY-SA 4.0 许可协议