Help with updating table with cummulative totals

I have a table with AMOUNT and MONTH columns. In a third column (CUMMULATIVE_AMOUNT), I’d like to update the table with the dynamic cummulative total for each month. I’m assuming this will have to be done, if at all possible, using a custom query. I would appreciate any help!


Hi Cynthia,

I hope I understand what you are trying to do. Sounds like you have a table with two columns and would like to pull the total of one of your columns in a query?

Assuming your amount field is an integer or decimal field, you can just use a regular query. You don’t need a third column. You can use an aggregate field and use the SUM feature.

I believe this is what she is trying to make happen

It is tricky because
Total Row 1 = Amount Row 1
Total Row 2 = Amount Row 1 + Amount Row 2
Total Row 3 = Total Row 2 + Amount Row 3 or Total Row 3 = Amount Row 1 + 2 +3

I have no clue how to do it but I think I am explaining it right

Sevenrice is correct. The total column is what I am calling the cummulative total.

@ccorley maybe it will help you :slight_smile:

use only custom query

SELECT *, SUM(test.AMOUNT) OVER(ORDER BY test.AMOUNT) AS cumulative_sum FROM testtable;

I found a solution that worked using the standard query builder. You will first need the same set of data (id, amount) in two different tables (t1 and t2).

Then you can set up the following:

SELECT, t1.amount FROM t1
GROUP BY, t1.amount

Why do you prefer a complicated solution when there is a simpler way? You can get the same result with the example I gave above.
Of course it’s your choice.
Good luck

Sorry, I made the change before I saw your reply. I’m a little hesitant to change things now since it’s working, but will definitely try your suggested next time. Thanks you your help!