用连接加总所有行

Question

如何用连接加总查询中的所有行？

SELECT SUM(accounting.amount) AS sum, SUM(balance_invoice.amount) AS sum_balance 
FROM accounting 
LEFT JOIN balance_invoice ON balance_invoice.accounting_id=accounting.id 

我需要这个查询与accounting.amount总和和balance_invoice.amount

多行只返回一行可以连接到每个accounting.id

更新

SELECT SUM(accounting.currency_amount*-1 + (
    SELECT SUM(balance_invoice_accounting.currency_amountoff) 
    FROM balance_invoice_accounting 
    WHERE balance_invoice_accounting.accounting_id=accounting.id 
)) AS sum 
FROM accounting 

Answer 1

只需添加SUM()-功能：

SELECT SUM(accounting.amount + balance_invoice.amount) AS sum_all 
FROM accounting 
LEFT JOIN balance_invoice ON balance_invoice.accounting_id=accounting.id 

如果列一个可以NULL，你应该添加一个额外的COALESCE()：

SELECT 
SUM(COALESCE(accounting.amount,0) + COALESCE(balance_invoice.amount,0) AS sum_all 
FROM accounting 
LEFT JOIN balance_invoice ON balance_invoice.accounting_id=accounting.id 

编辑：
我很抱歉，我错过了重要的作用。如果要统计每个accounting.amount只有一次，而可以有多个balance_invoice.amount加入到它，我会使用一个子查询是这样的：

SELECT 
    a.id, 
    (
    a.amount 
    + 
    (SELECT SUM(b.amount) FROM balance_invoice b WHERE b.accounting_id = a.id) 
) AS sum_all 
FROM 
    accounting a