mysql running total, order by date

Question

I have been trying to do a running total "order by date". The problem is that if you have two entries with the same date, then it shows the same total on both rows.

Table structure is something like this

sipID|Date|Amount
1|2017-11-07|2
2|2017-11-09|27
3|2017-11-07|24

So I know how to do a running total by id like this.

SELECT `sipID`,DATE_FORMAT(`Date`,'%d %M %Y') as `DateFormat`,`Amount`,
(SELECT SUM(`Amount`) FROM `salesinvoice_payments` as `Lin` WHERE `Lin`.`sipID`<=`Lout`.`sipID` && `salesinvoice_id`=115) as `Balance`
FROM `salesinvoice_payments` as `Lout`
WHERE `salesinvoice_id`=115
ORDER BY `sipID`

But I wanted it to be ordered by date ascending so I did this

SELECT `sipID`,DATE_FORMAT(`Date`,'%d %M %Y') as `DateFormat`,`Amount`,
(SELECT SUM(`Amount`) FROM `salesinvoice_payments` as `Lin` WHERE `Lin`.`Date`<=`Lout`.`Date` && `salesinvoice_id`=115) as `Balance`
FROM `salesinvoice_payments` as `Lout`
WHERE `salesinvoice_id`=115
ORDER BY `Date` ASC

Now this does work. The problem happens when you have 2 rows with the same date. What happens then is that both rows have the same running total. eg

07 November 2017    2.00    24.00
07 November 2017    22.00   24.00
09 November 2017    3.00    27.00

What I want is this

07 November 2017    2.00    2.00
07 November 2017    22.00   24.00
09 November 2017    3.00    27.00

Is there a workaround so that I can have a proper running total, and have it ordered by date without it getting the same total on the same date?

EDIT: @Strawberry: I have finally worked out how sqlfiddle works, and put this for my original question. http://www.sqlfiddle.com/#!9/c6dc75/1

It looks like you already have the running total, but you want to decompose it into its original parts. Is that correct? — Tim Biegeleisen, Nov 13 '17 at 15:22
See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Nov 13 '17 at 15:23
Hi Tim my second query gives a running total for the date, but because there are two dates which are the same it is giving the same total on both dates. I know why it is doing this, but I would rather it didn't — Thomas Williams, Nov 13 '17 at 15:25
if you have two order with the same date, which one you want first? You can have runnning totals `(2,24)` and `(22,24)` — Juan Carlos Oropeza, Nov 13 '17 at 15:38
Use user variables such as: https://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql instead of running a separate query for each date. — xQbert, Nov 13 '17 at 15:40
I have no preference which date goes first if they are the same date, as long as they show different values in the running order. @Strawberry : I am trying to get sqlfiddle to work, and will do the MCVE thing once I have worked it out — Thomas Williams, Nov 13 '17 at 15:41
0+2 = 2, 2+24 = 26, 26+3 = 29. Yep. top data says 24 bottom data says 22. leading to confusion; but it's a running total nevertheless... uservariables IMO. — xQbert, Nov 13 '17 at 15:44
Thanks Juan. I was trying to get sqlfiddle to work, but it kept saying I had an error. I might try rextester next time. I am going to learn how to use one of them so that I can post correctly next time — Thomas Williams, Nov 13 '17 at 16:25

score 3 · Accepted Answer · answered Nov 13 '17 at 15:45

You need something to separate Ammount from same day.

SQL DEMO

SELECT `sipID`,
        DATE_FORMAT(`Date`,'%d %M %Y') as `DateFormat`,
        `Amount`,
         (SELECT SUM(`Amount`) 
          FROM `salesinvoice_payments` as `Lin` 
          WHERE `Lin`.`Date` < `Lout`.`Date` 
            OR  (`Lin`.`Date` = `Lout`.`Date` 
                 AND `Lin`.`sipID` <= `Lout`.`sipID`)
          ) as `Balance`
FROM `salesinvoice_payments` as `Lout`
ORDER BY `Date` ASC

OUTPUT

xQbert · Answer 2 · 2017-11-14T16:05:33.700

1

Approach using user variables: DEMO with nod to Juan for setup of tables/data

SELECT `sipID`
     , DATE_FORMAT(`Date`,'%d %M %Y') as `DateFormat`
     ,`Amount`
     ,@Bal:=@Bal+`amount` as `Balance`
FROM `salesinvoice_payments` as `Lout`
CROSS JOIN (SELECT @Bal:=0) z --initializes and declares variable in select
WHERE `salesinvoice_id`=115
ORDER BY `Date`, `sipID`

Or using pagination

SELECT * 
FROM (SELECT `sipID`
           , DATE_FORMAT(`Date`,'%d %M %Y') as `DateFormat`
           ,`Amount`
           ,@Bal:=@Bal+`amount` as `Balance`
      FROM `salesinvoice_payments` as `Lout`
      CROSS JOIN (SELECT @Bal:=0) z --initializes and declares variable in select
      WHERE `salesinvoice_id`=115
      ORDER BY `Date`, `sipID`) Z
ORDER BY `DateFormat`, `sipID`
LIMIT 5,10;  -- Retrieve rows 6-15  Skip 5 retrieve next 10.

In this approach the variables have already been resolved in set {Z} thus the user variables are not impacted by the Limit/offset.

Example using your SQL Fiddle but we would need more sample data to really see the pagination impact.

edited Nov 14 '17 at 16:05

answered Nov 13 '17 at 15:48

xQbert

34,733
2
41
62

This probably has a better performance. Instead of multiple subquery just a single table scan. OP should test both. – Juan Carlos Oropeza Nov 13 '17 at 15:52
ah but we never care about performance! :P Get it work work first, then laugh... then do it right... then make it better. – xQbert Nov 13 '17 at 15:54
Hi xQbert. I will try yours after,but I am doing this in mysql php and variables don't tend to work very well under this situation. Also I don't think variables will work with my pagination clause. – Thomas Williams Nov 13 '17 at 16:26
Hi xQbert I know your method is less intensive but I usually have a LIMIT after the query for pagination eg it might be 0,10 on the first page, then change on the second page. So by using the variable on the second page I will lose the results on the first page. I only list 10 items at a time anyway in the pagination, so it shouldn't be a major performance hit. I may use your code on a project without pagination though. Thanks – Thomas Williams Nov 13 '17 at 20:53
Well you could wrap the query in a query for pagination. So the entire set is calculated and then the pagination takes effect on the set having the balance generated in a derived table and the user variables are no longer relevant. Always use what works for you and your situation! Both are responses to the question and both seem to work. and I honestly don't know if this would be faster. It uses less queries but I'm sure there's a cost to the derived table and ordering twice. Best of luck! – xQbert Nov 13 '17 at 21:10
Thanks I will give this a go tomorrow and wiegh up what works best. At least I have options now. This problem has been bugging me for a while. I have written some pretty advanced queries in the past, but sometimes things get the better of me. – Thomas Williams Nov 13 '17 at 23:26

mysql running total, order by date

2 Answers2