CODEWITHSUNDEEP
postgresql
wwadge
wwadge

Reputation: 3544

Obtaining a date-bound running total on postgresql

I have a database query running on Postgresql 9.3 that looks like this in order to obtain a running balance of accounting entries:

select  *,(sum(amount) over(partition 
by
    ae.account_id 
order by 
    ae.date_posted,
    ae.account_id
  )) as formula0_1_ 
from
    account_entry as ae 
--    where ae.date_posted> '2014-01-01'
order by account_id desc, date_posted asc

expected output without the where clause would be:

id | date         | amount | running balance
 1    2014-01-01      10          10
 2    2014-01-02      10          20

what I'm getting with the where clause:

id | date         | amount | running balance
 2    2014-01-02      10          10

How can I make this this query return me the same correct results if I try filtering by a date range (the bit commented above)?

Upvotes: 0

Views: 60

Answers (1)

Kouber Saparev
Kouber Saparev

Reputation: 8105

You need to select and calculate your running balances first over all the data, and then put a WHERE clause in an outer SELECT.

SELECT
  *
FROM
  (SELECT
    *,
    SUM(amount) OVER (
      PARTITION BY
        ae.account_id
      ORDER BY
        ae.date_posted,
        ae.account_id
    ) AS formula0_1_
  FROM
    account_entry AS ae) AS total
WHERE
  total.date_posted > '2014-01-01'
ORDER BY
  account_id DESC,
  date_posted ASC;

Upvotes: 1

Related Questions