CODEWITHSUNDEEP
mysqljoingroup-by
clarkk
clarkk

Reputation: 27689

sum all rows with join

How to sum all rows in a query with a join?

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

I need this query to return only one row with the total sum of accounting.amount and balance_invoice.amount

Multiple rows can be joined to each accounting.id

update

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

Upvotes: 1

Views: 341

Answers (1)

oezi
oezi

Reputation: 51807

just add both values inside of the SUM()-function:

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

if one of the columns can be NULL, you should add an additional 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

EDIT:
i'm sorry i missed that important part. if you want to count every accounting.amount only once while there can be multiple balance_invoice.amount joined to it, i'd use a subselect like this:

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

Upvotes: 1

Related Questions