Get 12 month running total for subgroups in Oracle

Question

I'm trying to get a running 12 month total for data, but changing when values in my fields change. I've made a simpler sql fiddle, for a 3 month running total, for one value in the table. I'm stuck on two parts: 1) how to get the running total, but have it change each time my field values change, and 2) how to then write that running total back to my table. (Or, I guess I could have a 2nd table and write to that instead. Yeah, that's probably easier.)

-- INIT database
CREATE TABLE Product (
  Bill_Date         DATE,
  LGroup            VARCHAR2(250 BYTE),
  District          VARCHAR2(50 BYTE),
  MonthTot          NUMBER(15,4),
  Last12Tot         NUMBER(18,4)
);

INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('05/01/2022','MM/DD/YYYY'), 'Sam','West', 13);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('06/01/2022','MM/DD/YYYY'), 'Sam','West', 10);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('07/01/2022','MM/DD/YYYY'), 'Sam','West', 11);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('08/01/2022','MM/DD/YYYY'), 'Sam','West', 8);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('05/01/2022','MM/DD/YYYY'), 'Chi','West', 21);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('06/01/2022','MM/DD/YYYY'), 'Chi','West', 19);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('07/01/2022','MM/DD/YYYY'), 'Chi','West', 15);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('08/01/2022','MM/DD/YYYY'), 'Chi','West', 11);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('05/01/2022','MM/DD/YYYY'), 'Emi','East', 8);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('06/01/2022','MM/DD/YYYY'), 'Emi','East', 5);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('07/01/2022','MM/DD/YYYY'), 'Emi','East', 6);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('08/01/2022','MM/DD/YYYY'), 'Emi','East', 4);

-- QUERY database
SELECT * FROM Product;
-- this will do a running total for one group
SELECT bill_date, LGroup, District, MonthTot
     , sum(MonthTot) over (order by bill_date, LGroup, District rows between 2 preceding and current row) as Last12Tot
from (
  select bill_date, LGroup, District, MonthTot
  from Product
  where LGroup = 'Sam'
      )
order by LGroup, District, bill_date
;

So my data set will look something like this:

BILL_DATE   LGROUP  DISTRICT    MONTHTOT
01-MAY-22   Sam     West        13   
01-JUN-22   Sam     West        10   
01-JUL-22   Sam     West        11   
01-AUG-22   Sam     West        8    
01-MAY-22   Chi     West        21   
01-JUN-22   Chi     West        19   
01-JUL-22   Chi     West        15   
01-AUG-22   Chi     West        11   
01-MAY-22   Emi     East        8    
01-JUN-22   Emi     East        5    
01-JUL-22   Emi     East        6    
01-AUG-22   Emi     East        4   

My current query returns this:

BILL_DATE   LGROUP  DISTRICT    MONTHTOT    LAST12TOT
01-MAY-22   Sam     West        13          13
01-JUN-22   Sam     West        10          23
01-JUL-22   Sam     West        11          34
01-AUG-22   Sam     West        8           29

I will not have one LGroup in multiple Districts, so LGroup is essentially what I need to group by. There isn't a primary key - it's already data assembled from a query to get the totals for each month for each person and district.

Every time I try to get all the groups, my partitioning is not working at all. I have multiple years and want a 12 month rolling total, but only within each group.

Once I have this, I can just sum my group totals to get district totals.

Answer 1

Use PARTITION BY and a RANGE window:

SELECT bill_date,
       LGroup,
       District,
       MonthTot,
       sum(MonthTot) over (
         PARTITION BY LGroup
         ORDER BY bill_date
         RANGE BETWEEN INTERVAL '11' MONTH PRECEDING AND CURRENT ROW
       ) AS Last12Tot
FROM   Product
order by LGroup, District, bill_date;

Which, for the sample data, outputs:

BILL_DATE	LGROUP	DISTRICT	MONTHTOT	LAST12TOT
2022-05-01 00:00:00	Chi	West	21	21
2022-06-01 00:00:00	Chi	West	19	40
2022-07-01 00:00:00	Chi	West	15	55
2022-08-01 00:00:00	Chi	West	11	66
2022-05-01 00:00:00	Emi	East	8	8
2022-06-01 00:00:00	Emi	East	5	13
2022-07-01 00:00:00	Emi	East	6	19
2022-08-01 00:00:00	Emi	East	4	23
2022-05-01 00:00:00	Sam	West	13	13
2022-06-01 00:00:00	Sam	West	10	23
2022-07-01 00:00:00	Sam	West	11	34
2022-08-01 00:00:00	Sam	West	8	42

fiddle