MySQL SUM previous row by date column using Union

Question

I am hoping I am just stumped because its the end of the work day on a Monday, and someone here can give me a hand.

Basically I have 2 tables that have invoice information and a table that has payment information. Using the following I get the first part of my display.

SELECT d.id, i.id as invid, i.company_id, d.total, created, adjustment FROM tbl_finance_invoices as i
LEFT JOIN tbl_finance_invoice_details as d ON d.invoice_id = i.id
WHERE company_id = '69350'
UNION
SELECT id, 0, comp_id, amount_paid, uploaded_date, 'paid' FROM tbl_finance_invoice_paid_items
WHERE comp_id = '69350'
ORDER BY created

What I want to do is:

Create a new column called "Balance" that adds total to the previous total by the created column regardless of how the rest of the table is sorted.

To give a quick example, my current output is something like:

id | invid | company_id | total | created | adjustment
12 | 16    | 1          | 40    | 01/01/16| 0
100| 0     | 1          | 10    | 01/05/16| 0
50 | 20    | 1          | 50    | 05/01/16| 0

What my goal is would be:

id | invid | company_id | total | created | adjustment | balance |Notes
12 | 16    | 1          | 40    | 01/01/16| 0          | 40      | 0 + 40
100| 0     | 1          | 10    | 01/05/16| 1          | 50      | 40 + 10
50 | 20    | 1          | 50    | 05/01/16| 0          | 100     | 50 + 50

And regardless of sorting by id, invid, total, created, etc, the balance would always be tied to the created date.

So if I added a "Where adjustment = '1'" to my sql, I would get:

    100| 0     | 1          | 10    | 01/05/16| 1          | 50      | 40 + 10

I'm sorry, but I do not understand your question! It would be nice if you could add some source data and expected outcome to your question! — Shadow, Feb 15 '16 at 21:41
I have just modified this and hopefully with my example it makes more sense. Thanks for the feedback you have already provided. — Kyle, Feb 15 '16 at 22:43
I think you want to do this: http://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql — , Feb 15 '16 at 22:47
@MartinBroadhurst running total **is** linked to ordering of the resultset - although this was my first thought as well. — Shadow, Feb 15 '16 at 22:49
@Kyle just to clarify: the running total would be tied to the order of created_date. The running total would only be affected by company id as a filtering criterion, all other filters should be disregarded for that calculation. — Shadow, Feb 15 '16 at 22:53
@Shadow - Yes I believe I am understanding you correctly. The running total is tied to the created date and is always going to have that "Where company_id = " filter. It is possible there are AND statements like I mentioned about the Adjustments though. (Where company_id = 1 AND adjustment = 1). — Kyle, Feb 15 '16 at 22:56

score 0 · Answer 1 · edited May 23 '17 at 10:28

Since the OP confirmed my understanding in comments, I'm basing my answer on the following assumption:

The running total would be tied to the order of created_date. The running total would only be affected by company id as a filtering criterion, all other filters should be disregarded for that calculation.

Since the running total may have a different order by and filtering criteria than the rest of the query, therefore the running total calculation has to be placed in a subquery.

The other assumption I have to make is that there cannot be more than one invoice with the same created date for a single customer id, since the original query in the OP does not have any group by or summing either.

I prefer to use the approach suggested by @OMG Ponies in this post on SO, where he initiates the mysql variable holding the running total in a subquery, thus there is no need to initialize the variable in a separate set statement.

SELECT d.id, i.id as invid, i.company_id, rt.total, rt.cumulative_sum, rt.created, adjustment 
FROM tbl_finance_invoices as i
LEFT JOIN tbl_finance_invoice_details as d ON d.invoice_id = i.id
LEFT JOIN
    (SELECT d.total, created, @running_total := @running_total + t.count AS cumulative_sum
    FROM tbl_finance_invoices as i
    LEFT JOIN tbl_finance_invoice_details as d ON d.invoice_id = i.id
    JOIN (SELECT @running_total := 0) r -- no join condition, so this produces a carthesian join
    WHERE company_id = '69350'
    ORDER BY created) rt
ON i.created=rt.created --this is also an assumption, I do not know which original table holds the created field
WHERE company_id = '69350' and adjustment=1  
ORDER BY d.id

If you need to take the amounts from the tbl_finance_invoice_paid_items into account as well, then you need to add that to the subquery.

MySQL SUM previous row by date column using Union

1 Answers1