AdvancedAdvanced PostgreSQL

Running balance (financial ledger query)

The query

SQL

SELECT
  transaction_date,
  description,
  amount,
  SUM(amount) OVER (
    ORDER BY transaction_date, transaction_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_balance,
  COUNT(*) OVER (
    ORDER BY transaction_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS transaction_number
FROM ledger
ORDER BY transaction_date, transaction_id;

Tested against PostgreSQL 16

Note

“Running balance is a classic financial query. ROWS UNBOUNDED PRECEDING ensures cumulative sum from start.