patternsqlMinor
Calculate Running Sum of Each Row From Start, Even When Filtering Records
Viewed 0 times
fromeachrecordsstartrunningcalculatesumevenrowwhen
Problem
I have this simplified table
I need to query the running sum (
I came up with a simple query:
and it displays the following:
Now, what I need to do is filter the results based on date, but the
i.e. if I were to add a
How can I accomplish this?
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 dateand 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?
Solution
Just wrap it in a subselect.
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
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;Code Snippets
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;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;Context
StackExchange Database Administrators Q#192553, answer score: 4
Revisions (0)
No revisions yet.