1

I am executing a cumulative sum order by date and I do not get the expected result. Some records are presented in a different order compared to the order used for the sum.

Please have a look at SQL Fiddle

I would expect either the following result:

2015-05-05T00:00:00Z 50 30 20 90 120

2015-05-05T00:00:00Z 60 30 30 120 100

2015-05-04T00:00:00Z 70 50 20 30 70

2015-05-04T00:00:00Z 80 40 40 70 50

2015-05-03T00:00:00Z 30 20 10 10

or the following order:

2015-05-05T00:00:00Z 60 30 30 120

2015-05-05T00:00:00Z 50 30 20 90

2015-05-05T00:00:00Z 60 30 30 120

2015-05-04T00:00:00Z 80 40 40 70

2015-05-04T00:00:00Z 70 50 20 30

2015-05-04T00:00:00Z 80 40 40 70

2015-05-03T00:00:00Z 30 20 10 10

(Added) please note that negative values are also possible. This is the reason why I have mentioned on answers below that an order on the cumulative sum would not solve the problem. As an example I will modify slightly the result:

2015-05-05T00:00:00Z 30 60 -30 60

2015-05-05T00:00:00Z 50 30 20 90

2015-05-04T00:00:00Z 80 40 40 70

2015-05-04T00:00:00Z 70 50 20 30

2015-05-03T00:00:00Z 30 20 10 10

Thanks for the help.

R Vilar
  • 11
  • 2
  • 2
    Your data isn't actually orderable because there is nothing unique about the dates and times. – P.Salmon Aug 01 '17 at 10:31
  • I agree, there are rows with the same date. But, what I find surprising, is that the order used for printing the result (rows with date 2015-05-04) is not the order used to make the cumulative sum (last column is the cumulative sum of the adjacent column). – R Vilar Aug 01 '17 at 16:05

2 Answers2

0

http://sqlfiddle.com/#!9/7204d4/2 gives your expceted output.. I have added @cum := @cum + tot_earn_pts - tot_redeem_pts asc after your query.

Ram Bhatt
  • 81
  • 1
  • 11
  • This is not solving the problem. It has change the order of the rows but the last column of each row is not the cumulative sum of the adjacent row. – R Vilar Aug 01 '17 at 15:57
0

Add extra fields in order by "cum_liability_pts" desc:

SQL Fiddle

SELECT *
  FROM (
    SELECT date,
           tot_earn_pts,
           tot_redeem_pts,
           tot_earn_pts - tot_redeem_pts AS tot_liability_pts,
           @cum := @cum + tot_earn_pts - tot_redeem_pts AS cum_liability_pts
      FROM (   
        SELECT date, 
               earn_points AS tot_earn_pts, 
               redeem_points AS tot_redeem_pts
          FROM i_report_total_order
       /* WHERE website_id = 36 */

           ) tots
      JOIN (SELECT @cum := 0) init
  ORDER BY date asc 
       ) res_asc
ORDER BY date desc, cum_liability_pts desc;
Vikram Jain
  • 5,498
  • 1
  • 19
  • 31