I have below data in a record, I want to update YTD totals in same table, unsure how to write this SQL

Actual Table

ID      Date        Total     YTD_Total
123   01/01/2022     50          
123   02/01/2022     25          
123   08/01/2023     60          
123   09/01/2023     50          
123   10/01/2023     20

Expected Results

ID      Date        Total     YTD_Total
123   01/01/2022     50          50
123   02/01/2022     25          75
123   08/01/2023     60          60
123   09/01/2023     50          110
123   10/01/2023     20          130

CodePudding user response：

I rarely need update statements, but this works and returns your expected results. You just need to partition by both ID and YEAR in your windows function.

update my_table a
 set ytd_total = (select running_total
  from (
   select
    id, 
    order_date, 
    sum(total) over (partition by id, extract(year from order_date) order by order_date) as running_total
   from my_table
  )b
where a.id = b.id
 and a.order_date = b.order_date
);
   

select *
from my_table

fiddle

CodePudding user response：

To calculate the running total for a column you can use analytic functions, aka window functions. The basic syntax is:

sum ( col ) over ( order by sort_col rows unbounded preceding )

SELECT id, date, total,
       sum (total) OVER (order by date) YTD_Total
  FROM my_table

That will answer your question based on your sample data. For more complex scenarios please have a read of the documentation I poined you to.

CodePudding user response：

I do not know, if I guessed your calculation- rules correct, but the following works (I used number instead of date, but it will work with date too)

Create and Fill Table:

DROP TABLE TABLE_XYZ
/
CREATE TABLE TABLE_XYZ (
  CID NUMBER(10) PRIMARY KEY,
  CIDGROUP NUMBER (10),
  CDATE NUMBER(2),
  CTOTAL NUMBER (3),
  CYTD_TOTAL NUMBER (10)
) 
/
INSERT INTO TABLE_XYZ SELECT 10, 123, 10, 50, NULL FROM DUAL;
INSERT INTO TABLE_XYZ SELECT 20, 123, 20, 25, NULL FROM DUAL;
INSERT INTO TABLE_XYZ SELECT 30, 123, 30, 60, NULL FROM DUAL;
INSERT INTO TABLE_XYZ SELECT 40, 123, 40, 50, NULL FROM DUAL;
INSERT INTO TABLE_XYZ SELECT 50, 123, 50, 20, NULL FROM DUAL;

INSERT INTO TABLE_XYZ SELECT 60, 124, 10, 1, NULL FROM DUAL;
INSERT INTO TABLE_XYZ SELECT 70, 124, 20, 2, NULL FROM DUAL;
INSERT INTO TABLE_XYZ SELECT 80, 124, 30, 3, NULL FROM DUAL;
INSERT INTO TABLE_XYZ SELECT 90, 124, 40, 4, NULL FROM DUAL;
INSERT INTO TABLE_XYZ SELECT 100, 124, 50, 5, NULL FROM DUAL;

And the UPDATE- Statement

UPDATE TABLE_XYZ OUTSIDE SET CYTD_TOTAL =
    ( 
        SELECT NEW_VALUE FROM (
            SELECT
                CID,
                SUM(CTOTAL) OVER (PARTITION BY CIDGROUP ORDER BY CDATE) NEW_VALUE
            FROM  
                TABLE_XYZ 
        ) INSIDE
        WHERE 
            INSIDE.CID = OUTSIDE.CID
    )  

CID CIDGROUP CDATE CTOTAL CYDT_TOTAL
 10      123    10    50          50
 20      123    20    25          75
 30      123    30    60         135
 40      123    40    50         185
 50      123    50    20         205
 60      124    10     1           1
 70      124    20     2           3
 80      124    30     3           6
 90      124    40     4          10
100      124    50     5          15