Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

      Invoices
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

UserBalances

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

Thanks

share|improve this question
    
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 Douglas 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.