I have the following sample data. HERE I need to get the cumulative sum of column abc-xyz and reqdcolumn per date, hour, shop. My mysql version is 5.7 Row over solution doesn't work for my mysql version.

Sample data

date      hour  Shop       abc     xyz  Diffabcxyz  ReqdColumn
---------------------------------------------------------------
20190428    1       1       0       0       0           0
20190428    2       1       5       4       1           0
20190428    3       1       15      8       7           1
20190428    4       1       16      9       7           8
20190428    5       1       14      13      1           15
20190428    6       1       8       12      -4          16
20190428    7       1       14      16      -2          12
20190428    8       1       9       7       2           10
20190428    9       1       5       2       3           12
20190428    10      1       6       2       4           15
20190428    1       2       0       0       0           0
20190428    2       2       7       1       6           0
20190428    3       2       5       -2      3           6
20190428    4       2       6       -5      1           9
20190428    5       2       4       7       -3          10
20190428    6       2       5       -8      -3          7
20190428    7       2       2       -9      -7          4
20190428    8       2       9       -10     -1          -3
20190428    9       2       6       4       2           -4
20190428    10      2       2       -12     -10         -2
20190428    1       3       0       0       0           0
20190428    2       3       6       -11     -5          0
20190428    3       3       8       -4      4           -5
20190428    4       3       5       -5      0           -1
20190428    5       3       9       8       1           -1
20190428    6       3       2       -1      1           0
20190428    7       3       4       4       0           1
20190428    8       3       1       2       -1          1
20190428    9       3       11      -4      7           0
20190428    10      3       0       1       -1          7

I tried to do a cumulative sum but didn't know how to group by the result set.

Tested codes:

Testing select

set @csum := 0;

SELECT 
    date, hour, shop, (@csum:[email protected] + Diffabcxyz)
FROM
    SampleTable
GROUP BY date, hour, shop;

testing update

set @csum := 0;
update sampletable
set reqdcolumn = (@csum := @csum + Diffabcxyz);

I need to group by the result set with date, time and shop.

Sample data and required result are here. ReqdColumn is the desired output

1 Answers

1
Michał Turczyn On Best Solutions

You can achieve SQL Server LAG functionalisty in order to get that cumulative sum:

select @lagDate := 0, @lagShop := 0, @diffLag := 0, @cumSum := 0;

select *,
       case when @lagDate = Date and @lagShop = Shop then @cumSum := @cumSum + @diffLag else @cumSum := 0 end,
       @diffLag := Diffabcxyz,
       @lagDate := Date,
       @lagShop := Shop
from tbl
order by shop, date, hour

Demo

UPDATE regarding comment:

Can you please tell me how can i get it to not do the cumulative count for the first 3 hours?

select @lagDate := 0, @lagShop := 0, @diffLag := 0, @cumSum := 0, @diffLagLag := 0;

select *,
       case when @lagDate = Date and @lagShop = Shop then @cumSum := @cumSum + @diffLagLag else @cumSum := 0 end,
       @diffLagLag := @diffLag,
       @diffLag := Diffabcxyz,
       @lagDate := Date,
       @lagShop := Shop
from tbl
order by shop, date, hour

Another demo

ANOTHER UPDATE:

select @lagDate := 0, @lagShop := 0, @diffLag := 0, @cumSum := 0;

select *,
       case when @lagDate = Date and @lagShop = Shop and Hour > 3 then @cumSum := @cumSum + @diffLag else @cumSum := 0 end,
       @diffLag := Diffabcxyz,
       @lagDate := Date,
       @lagShop := Shop
from tbl
order by shop, date, hour

Yet another demo