MySQL Forums
Forum List  »  Newbie

cumulative query grouped by createdate,warehouse,country and location
Posted by: John Martins
Date: October 02, 2018 06:18AM

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

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.