SUM over with distinct

Question

I am trying to sum over partition with distinct values from GameID column. It should be partitioned over CasinoID column, but it shouldn't sum up amounts with same GameID.
This is what I am aiming for

Date	CasinoID	GameID	Amount	TARGETAMOUNT
2020-01-01	1	1	1000	1000
2020-01-01	1	4	2000	3000
2020-01-01	1	2	300	3300
2020-01-01	1	3	1000	4300
2023-01-07	1	3	500	3800
2023-01-10	1	1	500	3300

Below are results.

Sum(amount) over (partition by CasinoID order by cast(date as date)) as TARGETAMOUNT

Date	CasinoID	GameID	Amount	TARGETAMOUNT
2020-01-01	1	1	1000	4300
2020-01-01	1	4	2000	4300
2020-01-01	1	2	300	4300
2020-01-01	1	3	1000	4300
2023-01-07	1	3	500	4800
2023-01-10	1	1	500	5300

Since those first four are from same day the TARGETAMOUNT is correct this way but I just showed at the above example to make it clearer what I was aiming for. But like it is shown below it sums bottom 2 rows to TARGETAMOUNT instead of replacing the amount value from the same GameID.

Sum(amount) over (partition by CasinoID, GameID order by cast(date as date)) as TARGETAMOUNT

This doesn't produce wanted end result either:

Date	CasinoID	GameID	Amount	TARGETAMOUNT
2020-01-01	1	1	1000	1000
2020-01-01	1	4	2000	2000
2020-01-01	1	2	300	300
2020-01-01	1	3	1000	1000
2023-01-07	1	3	500	1500
2023-01-10	1	1	500	1500

Answer 1

An alternative way of looking at it is that for each row you want to add the new amount, but also take away that game's previous amount.

You can get the previous amount with LAG() and then use SUM(amount - previous_amount).

CREATE TABLE moneys (
  game_date   DATE,
  casino_id   INT,
  game_id     INT, 
  amount      INT
)

INSERT INTO
  moneys
VALUES
  ('2024-01-01', 1, 1, 1000),
  ('2024-01-01', 1, 4, 2000),
  ('2024-01-01', 1, 2,  300),
  ('2024-01-01', 1, 3, 1000),
  ('2024-01-07', 1, 3,  500),
  ('2024-01-10', 1, 1,  500),
  ('2024-01-11', 1, 1,  100)

7 rows affected

WITH
  lagged AS
(
  SELECT
    *,
    LAG(amount, 1, 0) OVER (
      PARTITION BY casino_id, game_id
          ORDER BY game_date
    )
      AS previous_amount
  FROM
    moneys
)
SELECT
  *,
  SUM(amount - previous_amount) OVER (
    PARTITION BY casino_id
        ORDER BY game_date, game_id
  )
FROM
  lagged

game_date	casino_id	game_id	amount	previous_amount	(No column name)
2024-01-01	1	1	1000	0	1000
2024-01-01	1	2	300	0	1300
2024-01-01	1	3	1000	0	2300
2024-01-01	1	4	2000	0	4300
2024-01-07	1	3	500	1000	3800
2024-01-10	1	1	500	1000	3300
2024-01-11	1	1	100	500	2900

fiddle