1

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?

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.