Search code examples
sqlsql-servert-sql

SUM over with distinct


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

Solution

  • 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