2

I 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

Is it possible to do this with one query, without using a trigger or temp variables or stored procedures? Also will work with pagination on table/grid.

I made this question from this i still don't have the answer for this.

2 Answers 2

3

You can try this bro, for a quick solution:

-- Run this at first to set starting value at zero 
SET @variable = 0;
-- Without Pagination
SELECT        ID, `In`, `Out`, @variable := @variable + (`In` - `Out`) `Balance`
FROM          <your_table>
ORDER BY      ID ASC;
-- With Pagination
SELECT        ID, `In`, `Out`, @variable := @variable + (`In` - `Out`) `Balance`
FROM          <your_table>
ORDER BY      ID ASC
LIMIT         0 /* INDEX FOR THE START */, 5 /* NUMBER OF DISPLAYED ITEMS */;

Sample Result:

enter image description here

I hope this one can help/guide you into your problem, cheers!

1
  • 2
    You must include an ORDER BY clause.
    – Rick James
    Commented Jan 27, 2017 at 6:04
3

As you have also tagged it with Postgres:

select id, 
       "in", 
       "out", 
       sum("in") over (order by id) - sum("out") over (order by id) as balance
from data
order by id;

The above is not Postgres specific, it's standard ANSI SQL using window functions.

Online example: http://rextester.com/ENBLS73027

To add pagination, use row_number()

select id, "in", "out", balance
from (
  select id, 
         "in", 
         "out", 
         sum("in") over (order by id) - sum("out") over (order by id) as balance, 
         row_Number() over (order by id) as rn
  from data
) t
where rn between 3 and 6 -- rows 3,4,5 and 6
order by id;
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.