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!
Cynthia
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 
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.id, t1.amount FROM t1
INNER JOIN t2 ON t1.id <= t2.id
GROUP BY t1.id, t1.amount
ORDER BY t1.id
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!
