Cumulative Sum grouped by multiple parameters

Question

I need Cumulative sum for 'Order Qty'.

When i use this code with Select fields as "Sales_Year","Sales_Billing_Date", "Order_Qty" am getting the result i needed.

Code used:

"Sales_Year",
"Sales_Billing_Date",
"Order_Qty" ,
SUM ("Order_Qty") OVER ( ORDER BY "Sales_Billing_Date") AS "SUM_VAL"
FROM
( SELECT 
"Sales_Year",
"Sales_Billing_Date" ,
SUM("Order_Qty") AS "Order_Qty" FROM "PS_WARRANTY_ANALYTICS"."PS_PAL_DLR_SALES"
  GROUP BY "Sales_Year","Sales_Billing_Date"
)

But i select more fields(added "Equipment_ID") its not getting grouped by as its unique field. how can in achieve cumulative value including this field code added

"Equipment_ID"
SELECT 
"Sales_Year",
"Sales_Billing_Date",
"Equipment_ID",
"Order_Qty" ,
SUM ("Order_Qty") OVER ( ORDER BY "Sales_Billing_Date") AS "SUM_VAL"
FROM
( SELECT 
"Sales_Year",
"Sales_Billing_Date" ,
"Equipment_ID",
SUM("Order_Qty") AS "Order_Qty" FROM "PS_WARRANTY_ANALYTICS"."PS_PAL_DLR_SALES"
  GROUP BY "Sales_Year","Sales_Billing_Date","Equipment_ID"
)

cumulated:

not getting cumulated:

SUM ("Order_Qty") OVER ( ORDER BY "Sales_Billing_Date windowing functions are not available in MYSQL until version 8 are you on version 8 or are you on sqlserver? — P.Salmon, Feb 02 '18 at 08:02
Yes partition by is available though i have not used before.can you pls is it possible in this case and how?? — Neeraja neithyar, Feb 02 '18 at 09:26
Are you looking to get the sum of all `SUM_VAL` values of all equipment combined for Jan 1, 2014 as `246` ? — Vash, Feb 03 '18 at 06:34
As Equipment ID is my Unique ID ,i need to combine this table with other table with equipment ID. Hence it is mandatory to add... But When i include Equipment ID here in my select statement, the cumulative sum is not accurate as shown in second image. — Neeraja neithyar, Feb 03 '18 at 07:18

score 0 · Answer 1 · answered Feb 02 '18 at 13:22

Assuming your sub query returns something like this

yyyy        orderdate               productid   sumqty
----------- ----------------------- ----------- -----------
2005        2005-07-01 00:00:00.000 707         24
2005        2005-08-01 00:00:00.000 707         58
2005        2005-09-01 00:00:00.000 707         55
2005        2005-07-01 00:00:00.000 708         27
2005        2005-08-01 00:00:00.000 708         56
2005        2005-09-01 00:00:00.000 708         57
2005        2005-07-01 00:00:00.000 709         38
2005        2005-08-01 00:00:00.000 709         134
2005        2005-09-01 00:00:00.000 709         79
2005        2005-07-01 00:00:00.000 710         5
2005        2005-08-01 00:00:00.000 710         13
2005        2005-09-01 00:00:00.000 710         6
2005        2005-07-01 00:00:00.000 711         33
2005        2005-08-01 00:00:00.000 711         64
2005        2005-09-01 00:00:00.000 711         49
2005        2005-07-01 00:00:00.000 712         40
2005        2005-08-01 00:00:00.000 712         103
2005        2005-09-01 00:00:00.000 712         83
2005        2005-07-01 00:00:00.000 714         16
2005        2005-08-01 00:00:00.000 714         37
2005        2005-09-01 00:00:00.000 714         35
2005        2005-07-01 00:00:00.000 715         49
2005        2005-08-01 00:00:00.000 715         114
2005        2005-09-01 00:00:00.000 715         72
2005        2005-07-01 00:00:00.000 716         19
2005        2005-08-01 00:00:00.000 716         48
2005        2005-09-01 00:00:00.000 716         40
2005        2005-07-01 00:00:00.000 722         8
2005        2005-08-01 00:00:00.000 722         20
2005        2005-09-01 00:00:00.000 722         7
2005        2005-07-01 00:00:00.000 725         15
2005        2005-08-01 00:00:00.000 725         38
2005        2005-09-01 00:00:00.000 725         29
2005        2005-07-01 00:00:00.000 726         9
2005        2005-08-01 00:00:00.000 726         19
2005        2005-09-01 00:00:00.000 726         7
2005        2005-07-01 00:00:00.000 729         16
2005        2005-08-01 00:00:00.000 729         41
2005        2005-09-01 00:00:00.000 729         25

(39 row(s) affected)

And assuming sap behaves like sqlserver then

select * ,
        sum(sumqty) over (partition by year(orderdate), orderdate order by productid ) cumtot
from
(
select year(orderdate) yyyy,orderdate,productid,sum(OrderQty) sumqty
from [AdventureWorks2012].[Sales].[SalesOrderHeader] soh
join [AdventureWorks2012].[Sales].[SalesOrderDetail] sod on sod.SalesOrderID = soh.SalesOrderID
where year(orderdate) = 2005 and month(orderdate) in (7,8,9) and day(orderdate) in (1,2,3)
    AND PRODUCTID < 730
group by year(orderdate),orderdate,productid
) s
order by yyyy,orderdate,productid

Produces

yyyy        orderdate               productid   sumqty      cumtot
----------- ----------------------- ----------- ----------- -----------
2005        2005-07-01 00:00:00.000 707         24          24
2005        2005-07-01 00:00:00.000 708         27          51
2005        2005-07-01 00:00:00.000 709         38          89
2005        2005-07-01 00:00:00.000 710         5           94
2005        2005-07-01 00:00:00.000 711         33          127
2005        2005-07-01 00:00:00.000 712         40          167
2005        2005-07-01 00:00:00.000 714         16          183
2005        2005-07-01 00:00:00.000 715         49          232
2005        2005-07-01 00:00:00.000 716         19          251
2005        2005-07-01 00:00:00.000 722         8           259
2005        2005-07-01 00:00:00.000 725         15          274
2005        2005-07-01 00:00:00.000 726         9           283
2005        2005-07-01 00:00:00.000 729         16          299
2005        2005-08-01 00:00:00.000 707         58          58
2005        2005-08-01 00:00:00.000 708         56          114
2005        2005-08-01 00:00:00.000 709         134         248
2005        2005-08-01 00:00:00.000 710         13          261
2005        2005-08-01 00:00:00.000 711         64          325
2005        2005-08-01 00:00:00.000 712         103         428
2005        2005-08-01 00:00:00.000 714         37          465
2005        2005-08-01 00:00:00.000 715         114         579
2005        2005-08-01 00:00:00.000 716         48          627
2005        2005-08-01 00:00:00.000 722         20          647
2005        2005-08-01 00:00:00.000 725         38          685
2005        2005-08-01 00:00:00.000 726         19          704
2005        2005-08-01 00:00:00.000 729         41          745
2005        2005-09-01 00:00:00.000 707         55          55
2005        2005-09-01 00:00:00.000 708         57          112
2005        2005-09-01 00:00:00.000 709         79          191
2005        2005-09-01 00:00:00.000 710         6           197
2005        2005-09-01 00:00:00.000 711         49          246
2005        2005-09-01 00:00:00.000 712         83          329
2005        2005-09-01 00:00:00.000 714         35          364
2005        2005-09-01 00:00:00.000 715         72          436
2005        2005-09-01 00:00:00.000 716         40          476
2005        2005-09-01 00:00:00.000 722         7           483
2005        2005-09-01 00:00:00.000 725         29          512
2005        2005-09-01 00:00:00.000 726         7           519
2005        2005-09-01 00:00:00.000 729         25          544

(39 row(s) affected)

Cumulative Sum grouped by multiple parameters

1 Answers1

Linked