3

I'ved got a problem same as this but I am using Postgres.

Calculate balance with mysql

have a table which contains the following data:

ID      In       Out 
1      100.00    0.00   
2       10.00    0.00   
3        0.00   70.00    
4        5.00    0.00    
5        0.00   60.00   
6       20.00    0.00

Now I need a query which gives me the following result:

ID      In       Out    Balance
1      100.00    0.00   100.00
2       10.00    0.00   110.00
3        0.00   70.00    40.00
4        5.00    0.00    45.00
5        0.00   60.00   -15.00
6       20.00    0.00     5.00

How best to handle "balance" calculation. I was told there is window function in postgres, how would this be done using postgres window functions ?

Thanks.

Community
  • 1
  • 1
Axil
  • 2,835
  • 5
  • 43
  • 97

1 Answers1

7
select t.*, sum("In"-"Out") over(order by id) as balance
from tbl t
order by id

Fiddle: http://sqlfiddle.com/#!15/97dc5/2/0

Consider changing your column names "In" / "Out" so that you don't need to put them in quotes. (They are reserved words)

If you wanted only one customer (customer_id = 2):

select t.*, sum("In"-"Out") over(order by id) as balance
from tbl t
where customer_id = 2
order by id

If your query were to span multiple customers and you wanted a running balance that RESTARTED with each customer, you could use:

select t.*, sum("In"-"Out") over( partition by customer_id
                                  order by customer_id, id ) as balance_by_cust
from tbl t
order by customer_id, id
Brian DeMilia
  • 12,331
  • 1
  • 19
  • 30
  • wow that was real quick! thanks a lot. just one more slight question, lets say there is a customer_id, and i want to query and calculate balances only for customer_id == 2, how would the statement be ? I cant get this to work: select t.*, sum(in-out) over(order by id) as balance where customer_id == "2" from tbl t order by id – Axil Aug 04 '14 at 01:55
  • @Axil in sql you use a single equal sign, see my edit. Also the where clause goes after the from clause. – Brian DeMilia Aug 04 '14 at 01:57
  • Hi Brian, one last question if I may, is there a way using the same statement to update balance column in the table. Right now the balance is virtual column, lets say there is really a "balance" column in the table, i want all those values you have generated updated to that particular row similar how you have displayed it. Thanks – Axil Aug 04 '14 at 05:49
  • @Axil I wouldn't physically store a balance column in the table since it's a computation. You might want to create a view, which is basically a stored sql statement, with that column. See http://www.postgresql.org/docs/9.2/static/sql-createview.html . Otherwise you would be constantly updating the table w/ additional transactions. A view -- made using the sql above -- would be best. – Brian DeMilia Aug 04 '14 at 20:30
  • @Axil here is an example of using a view. http://sqlfiddle.com/#!15/058a2/1/0 , after the view is created, using the above sql, you can just query against that view the way you would any other table (essentially getting what you want, but you wouldn't have to constantly update it). – Brian DeMilia Aug 04 '14 at 20:32
  • Thanks, i think agree with you, Im having difficulty accepting the design of storing balances. It just doesnt seemed elegant. For some reason i need to be checking balances for each financial periods. Triggers and storing, just doesnt seem right. One way Im thinking is only to store opening balances, that would probably be more idea than for every single transactions. Thanks again. – Axil Aug 06 '14 at 00:52