[SQL] MYSQL Running Balance Initialization Value Not Correct

I have a query which adds the invoices (negative value) and payments to create a net value, and to provide a running balance (using @balance = -invoice amount + payment). Code below:

SELECT id, book_id, type, `date`, inv_total, payment,
payment + inv_total AS net, -- Calculate net directly
@balance := @balance + (payment + inv_total) AS balance -- Use net calculation inline
FROM (
-- First subquery for invoices
SELECT
inv_id AS id, invoices.book_id AS book_id, due_date AS `date`,
(-(amount)) AS inv_total, -- Invoice as negative
0 AS payment, -- No payment for this row
'inv' AS type -- Mark this row as an invoice
FROM
invoices
UNION ALL
-- Second subquery for payments
SELECT
pay_id AS id, payments.account_id AS book_id, `date`,
0 AS inv_total, -- No invoice total for this row
amount AS payment, -- Payment value
'pay' AS type -- Mark this row as a payment
FROM
payments
) AS combined
CROSS JOIN (SELECT @balance := 0) AS init
ORDER BY `date`, id;


The query result below. It shows balance in first row at -3800. I would have expected 100 same as the first net value. Why is the initialization incorrect?



Continue reading...