I need to calculate the running total in which the target column is changing along with the time. Its something which aws does. Charging based for only what you use. So how do I aggregate sum of payments and charges.

Currently I'm storing the running total in the balance column and that sum is calculated using sum of charges - sum of payments.

Here's what the table looks like

id    tx_type    amount   live 
 1     Charge     100      true             // live = true means that the amount will be updated and the meter is ticking
 2     Payment    100      false
 3     Charge     50       true
 4     Charge     75       false
 5     Payment    125      false


id  user_id   balance
1     1         0.0

So the balance at this instant will be 0 but after couple of seconds should be different as meter is still ticking. How do I get the sum of amount field which is ever changing?

PS: I'm using ruby on rails and postgresql


  • I'm not sure I understand what you want to achieve. Is your aim to update the totals automagically when a live item changes? In this case you can either add this functionality to your existsing code or create a trigger on Invoices which will look after the totals. – dezso Mar 1 '13 at 10:18
  • Is your aim to update the totals automagically when a live item changes? Yes. But is it advisable to perform sum query over 10000s records and storing it in the UserBlances table(perhaps not on each update)? – benchwarmer Mar 1 '13 at 10:25
  • It depends on your use case. If there are, say, 1000 updates per second, then the answer is no. If only one, then possibly yes. From an other point of view, if you do the update on every change, you have only to add/substract the change of the amount. – dezso Mar 1 '13 at 10:36
  • You may find some useful pointers in the answers to this question – Jack says try topanswers.xyz Mar 1 '13 at 11:05
  • 2
    Your table would need a date/time column to see a running total ordered by one. Also, do you really need a running total over every second, or do you just need it at specific points along the way. In either case it seems like you would need to have the rate of increase. – Leigh Riffel Mar 1 '13 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.