cumulative query grouped by createdate,warehouse,country and location
Hello together,
I´m looking for a cumulative sum of the following output:
country; warehouse; createdate; location; quantity;
AT; 2010; 2018-09-20; ; 1;
AU; 2029; 2018-09-17; ; 2;
AU; 2020; 2018-09-10; ; 1;
BE; 2020; 2018-06-05; ; 2;
BE; 2020; 2018-03-30; ; 1;
BE; 2020; 2018-03-22; ; 1;
BR; 2020; 2018-05-03; ; 1;
CH; 2020; 2018-07-02; ; 1;
CL; 2020; 2018-01-05; ; 1;
CL; 2020; 2017-11-29; ; 1;
CN; 2029; 2018-07-17; ; 2;
CN; 2029; 2018-08-08; ; 4;
CN; 2029; 2018-08-25; ; 6;
CN; 2029; 2018-06-25; ; 2;
CN; 2029; 2018-07-12; ; 1;
CN; 2029; 2018-08-02; ; 3;
CZ; 2010; 2018-03-14; ; 1;
CZ; 2010; 2018-07-13; ; 1;
CZ; 2020; 2018-03-23; ; 1;
CZ; 2020; 2018-01-23; ; 1;
CZ; 2010; 2018-03-28; ; 1;
DE; 2020; 2018-01-15; 1131; 1;
DE; 2020; 2018-04-25; 1150; 1;
DE; 2020; 2018-09-10; 1141; 1;
DE; 2020; 2018-04-19; 1114; 1;
DE; 2020; 2018-05-29; 1113; 1;
DE; 2020; 2018-08-20; 1121; 1;
DE; 2020; 2018-03-05; 1173; 1;
DE; 2020; 2018-03-22; 1142; 1;
DE; 2020; 2018-04-16; 1141; 1;
here is the query related to the output above:
select lkz as country, werk as warehouse, credatum as createdate, if(lkz='DE',nl,'') as location, count(distinct auf_pos) as quantity from (select min(p.credatum) as credatum, p.werk, p.Auftrag_Nr, auf_pos, p.nl, k.lkz, sum(menge_vme) as menge_vme from sap.lieferpos p, sap.lieferkopf k
where p.lieferschein_nr=k.lieferschein_nr and ifnull(p.absage,'') = '' and ZielWaDatumCO between ADDDATE('2018-09-21',-366) and '2018-09-21' group by p.werk,lkz,p.nl,p.Auftrag_Nr,auf_pos order by p.credatum) uebf
where ifnull(menge_vme,0)=0
group by createdate,warehouse,country,location order by country
;
The cumulative quantity have to be grouped by createdate, warehouse, country and location. Here is an example how the ouput supposed to be (without the column quantity):
country; warehouse; createdate; location; quantity; cum;
AT; 2010; 2018-09-20; ; 1; 1;
AU; 2029; 2018-09-17; ; 2; 2;
AU; 2020; 2018-09-10; ; 1; 1;
BE; 2020; 2018-06-05; ; 2; 2;
BE; 2020; 2018-03-30; ; 1; 3;
BE; 2020; 2018-03-22; ; 1; 4;
BR; 2020; 2018-05-03; ; 1; 1;
CH; 2020; 2018-07-02; ; 1; 1;
CL; 2020; 2018-01-05; ; 1; 1;
CL; 2020; 2017-11-29; ; 1; 2;
CN; 2029; 2018-07-17; ; 2; 2;
CN; 2029; 2018-08-08; ; 4; 6;
CN; 2029; 2018-08-25; ; 6; 12;
CN; 2029; 2018-06-25; ; 2; 14;
CN; 2029; 2018-07-12; ; 1; 15;
CN; 2029; 2018-08-02; ; 3; 18;
CZ; 2010; 2018-03-14; ; 1; 1;
CZ; 2010; 2018-07-13; ; 1; 2;
CZ; 2020; 2018-03-23; ; 1; 1;
CZ; 2020; 2018-01-23; ; 1; 2;
CZ; 2010; 2018-03-28; ; 1; 3;
DE; 2020; 2018-01-15; 1131; 1; 1;
DE; 2020; 2018-04-25; 1150; 1; 1;
DE; 2020; 2018-09-10; 1141; 1; 1;
DE; 2020; 2018-04-19; 1114; 1; 1;
DE; 2020; 2018-05-29; 1113; 1; 1;
DE; 2020; 2018-08-20; 1121; 1; 1;
DE; 2020; 2018-03-05; 1173; 1; 1;
DE; 2020; 2018-03-22; 1142; 1; 1;
DE; 2020; 2018-04-16; 1141; 1; 2;
The column "cum" contains the cumulative quantity.
I tried to accumulate with the following query:
select warehouse, createdate, country, location, (@i:=@i+quantity) as quantity_cumul from (select lkz as country, werk as warehouse, credatum as createdate, if(lkz='DE',nl,'') as location, count(distinct auf_pos) as quantity from (select min(p.credatum) as credatum, p.werk, p.Auftrag_Nr, auf_pos, p.nl, k.lkz, sum(menge_vme) as menge_vme from sap.lieferpos p, sap.lieferkopf k
where p.lieferschein_nr=k.lieferschein_nr and ifnull(p.absage,'') = '' and ZielWaDatumCO between ADDDATE('2018-09-21',-366) and '2018-09-21' group by p.werk,lkz,p.nl,p.Auftrag_Nr,auf_pos order by p.credatum) uebf
where ifnull(menge_vme,0)=0
group by createdate,warehouse,country,location order by createdate) rstd
;
but I don´t get the expected result.
Thanks for your help.
John