Calculating the cumulative sum with a specific ‘date’ merged to single column in PostgreSQL

I have a database which contains the amounts and dates per user paid. Now some users make payments on the same day and I want to show the cumulative sum of these payments only once per day in a pivot table, which I am creating using Amazon QuickSight.

I have gone through the following, but they provide the cumulative values once per row and I don’t have a way to partition on just the date and not on anything else, with the sum over the payment made.

Calculating Cumulative Sum in PostgreSQL

Calculating cumulative sum with date filtering in PostgreSQL

Calculating Cumulative daily sum in PostgreSQL

PostgreSQL, renumber and cumulative sum at once

How to conditional sum two columns in PostgreSQL 9.3

My query looks like this:

SELECT
    s.id,
    s.first_name,
    s.last_name,
    s.birth_date,
    s.card,
    p.datetime,
    p.amount,
    Sum(p.amount)OVER(partition BY p.datetime ORDER BY p.datetime ) AS "Daily Amount"

FROM payments AS p

LEFT JOIN users AS s
ON p.s_h_uuid = s.h_uuid

ORDER BY p.datetime DESC

Where I am doing a Sum() Over() at this row:

Sum(pa.amount)OVER(partition BY p.datetime ORDER BY p.datetime ) AS "Daily Amount"

My Table has data as:

Users:

| id | first_name | last_name | birth_date | card |
| 2  | first_nam2 | last_nam2 | 1990-02-01 | M    |
| 3  | first_nam3 | last_nam3 | 1987-07-23 | M    |
| 1  | first_nam1 | last_nam1 | 1954-11-15 | A    |
| 4  | first_nam4 | last_nam4 | 1968-05-07 | V    |

Payments:

| p_uuid | datetime   | amount |
| 2      | 2021-05-01 | 100.00 |
| 3      | 2021-05-01 | 100.00 |
| 2      | 2021-05-02 | 100.00 |
| 1      | 2021-05-03 | 100.00 |
| 3      | 2021-05-03 | 100.00 |
| 4      | 2021-05-03 | 100.00 |
| 2      | 2021-05-05 | 100.00 |
| 1      | 2021-05-05 | 100.00 |
| 4      | 2021-05-06 | 100.00 |

The output I want is that the “Daily Amount” is shown only once for a specific date, if there are multiple rows with the same date, then for the other rows, it should be blank or display something like “NA”:

| p.datetime | id | first_name | last_name | birth_date | card | pa.amount | "Daily Amount" |
| 2021-05-01 | 2  | first_nam2 | last_nam2 | 1990-02-01 | M    | 100.00    | 200.00         |
| 2021-05-01 | 3  | first_nam3 | last_nam3 | 1987-07-23 | M    | 100.00    |                |
| 2021-05-02 | 2  | first_nam2 | last_nam2 | 1990-02-01 | M    | 100.00    | 100.00         |
| 2021-05-03 | 1  | first_nam1 | last_nam1 | 1954-11-15 | A    | 100.00    | 300.00         |   
| 2021-05-03 | 3  | first_nam3 | last_nam3 | 1987-07-23 | M    | 100.00    |                |
| 2021-05-03 | 4  | first_nam4 | last_nam4 | 1968-05-07 | V    | 100.00    |                |
| 2021-05-05 | 2  | first_nam2 | last_nam2 | 1990-02-01 | M    | 100.00    | 200.00         |
| 2021-05-05 | 1  | first_nam1 | last_nam1 | 1954-11-15 | A    | 100.00    |                |
| 2021-05-06 | 4  | first_nam4 | last_nam4 | 1968-05-07 | V    | 100.00    | 100.00         |

Is there some way that it is possible to get this output from SQL (PostgreSQL specific query)?

Answer

Looks like your sum() over() computes the wrong amount, try

 Sum(p.amount) OVER(partition BY s.id, p.datetime) AS "Daily Amount",

EDIT If you want to format output (cumulative amount only once per date), use row_number() to detect first row in a group. Make sure over() clause is in sync with ORDER BY of the query.

SELECT 
        id,
        first_name,
        last_name,
        birth_date,
        card,
        datetime,
        amount,
        case when rn=1 then "Daily Amount" end "Daily Amount" 
FROM (
    SELECT
        s.id,
        s.first_name,
        s.last_name,
        s.birth_date,
        s.card,
        p.datetime,
        p.amount,
        Sum(p.amount) OVER(partition BY s.id, p.datetime) AS "Daily Amount",
        row_number() OVER(partition BY s.id, p.datetime ORDER BY p.amount) AS rn
    FROM payments AS p
    LEFT JOIN users AS s ON p.s_h_uuid = s.h_uuid
) t
ORDER BY datetime DESC, id, amount

Advertisement

Answer