Postgresql – Calculate column running total

postgresqlwindow functions

Given the following schema and dummy data:

CREATE TABLE transactions (
  id SERIAL PRIMARY KEY,
  memo VARCHAR(255) NOT NULL,
  amount INT NOT NULL,
  tx_date DATE NOT NULL
);

INSERT INTO transactions (memo, amount, tx_date) VALUES
  ('Initial balance', 10000, '2015-08-17'),
  (     'Eating out', -1000, '2015-08-19'),
  (            'Gas', -7000, '2015-08-19'),
  (          'Wages', 75000, '2015-08-31');

I'm trying to generate a report of the running balance ordered by the transaction date such as:

 id |      memo       | amount |  tx_date   | running_balance 
----+-----------------+--------+------------+-----------------
  4 | Wages           |  75000 | 2015-08-31 |           77000
  2 | Eating out      |  -1000 | 2015-08-19 |            2000
  3 | Gas             |  -7000 | 2015-08-19 |            3000
  1 | Initial balance |  10000 | 2015-08-17 |           10000

Ideally I'd like to sum each amount to the running_balance of the following row, but it seems that the lead() window function can't reference the very same column you are trying to create:

SELECT *, amount + lead(running_balance, 1, 0) OVER (ORDER BY tx_date DESC) AS running_balance
FROM transactions
ORDER BY tx_date DESC;

ERROR:  column "running_balance" does not exist
LINE 1: SELECT *, amount + lead(running_balance, 1, 0) OVER (ORDER B...

Another option would be to make partial column sums from the current row to the last one, but I am also unable to come up with the correct syntax:

SELECT *, SUM(amount) OVER (ORDER BY tx_date DESC ROWS UNBOUNDED FOLLOWING) AS running_balance
FROM transactions
ORDER BY tx_date DESC;

ERROR:  frame start cannot be UNBOUNDED FOLLOWING
LINE 1: ...T *, SUM(amount) OVER (ORDER BY tx_date DESC ROWS UNBOUNDED ...

Can any of these queries/approaches be made to work? Or is there any other way to achieve the desired result?

Best Answer

Well, apparently this is it:

SELECT 
    *, 
    SUM(amount) OVER (
        ORDER BY tx_date DESC 
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS running_balance
FROM transactions
ORDER BY tx_date DESC;

Output:

 id |      memo       | amount |  tx_date   | running_balance 
----+-----------------+--------+------------+-----------------
  4 | Wages           |  75000 | 2015-08-31 |           77000
  2 | Eating out      |  -1000 | 2015-08-19 |            2000
  3 | Gas             |  -7000 | 2015-08-19 |            3000
  1 | Initial balance |  10000 | 2015-08-17 |           10000
Related Question