4

I have this simplified table entries:

----------------------------
| id | date       | amount |
----------------------------
| 1  | 2017-12-01 | 100    |
----------------------------
| 2  | 2017-12-03 | 100    |
----------------------------
| 3  | 2017-12-05 | 100    |
----------------------------

I need to query the running sum (balance) of the amount column alongside other columns.

I came up with a simple query:

SELECT id, date, amount, SUM(amount) OVER (ORDER BY date) AS balance
FROM entries
GROUP BY id, date, amount
ORDER BY date

and it displays the following:

--------------------------------------
| id | date       | amount | balance |
--------------------------------------
| 1  | 2017-12-01 | 100    | 100     |
-------------------------------------
| 2  | 2017-12-03 | 100    | 200     |
-------------------------------------
| 3  | 2017-12-05 | 100    | 300     |
-------------------------------------

Now, what I need to do is filter the results based on date, but the balance column still needs to be calculated from the start date i.e. 2017-12-01 (or from the first record in the table).

i.e. if I were to add a WHERE date >= '2017-12-03', I'd still want the balance of each row to be same as it was without the date range:

--------------------------------------
| id | date       | amount | balance |
--------------------------------------
| 2  | 2017-12-03 | 100    | 200     |
-------------------------------------
| 3  | 2017-12-05 | 100    | 300     |
-------------------------------------

How can I accomplish this?

Improve this question

1 Answer 1

Reset to default
4

Just wrap it in a subselect.

SELECT *
FROM (
  SELECT id, date, amount, SUM(amount) OVER (ORDER BY date, id) AS balance
  FROM entries
  GROUP BY id, date, amount
)
WHERE date >= '2017-12-03'
ORDER BY date, id;

I'm also confused about a few things if you have three rows you don't have to group by it, see if this gives you the same result, it should be a lot faster.

SELECT *
FROM (
  SELECT id, date, amount, SUM(amount) OVER (ORDER BY date, id) AS balance
  FROM entries
)
WHERE date >= '2017-12-03'
ORDER BY date, id;
Improve this answer
10
  • If I don't add id in the group by clause PostgreSQL complains - "entries.id" must appear in the GROUP BY clause or be used in an aggregate function.
    – Code Poet
    Commented Dec 6, 2017 at 18:33
  • Also, I need a chronological ordering of records, ordering by id won't give me that, because records can be inserted out of order. That is back-dated, forward-dated, etc.
    – Code Poet
    Commented Dec 6, 2017 at 18:35
  • @CodePoet you shoudln't need a group by anywhere in there. Try the query provided. If id isn't chronological then you're right. you'll need to order by date.
    – Evan Carroll
    Commented Dec 6, 2017 at 18:37
  • 2
    Note that if date isn't unique (if there are more than one rows with same date or any chance of that happening), then your ORDER BY needs adjustment for the result to be completely deterministic. E,g, OVER (ORDER BY date, id). Otherwise you may get slightly different results per execution.
    – ypercubeᵀᴹ
    Commented Dec 6, 2017 at 18:58
  • 1
    What I'm suggesting will order by id in that case, yes. No idea if that is aligned with creation order. You could order by anything you like really, as long as the order is complete/deterministic, meaning that any two rows will be deterministaclly placed (in that order). If you have a not complete order like ORDER BY date, then the tied rows can appear in any order (between them) and the rolling sum can be calculated in any order (between them). The results wouldl appear to be changing.
    – ypercubeᵀᴹ
    Commented Dec 6, 2017 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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