Adding or subtracting value depending on value of two columns-CodePudding

I have a table where I already implemented some query to create two columns where one column is equal to zero while the other is not. It can look like this

...| a | b |
   | 1 | 0 |
   | 0 | 1 |
   | 1 | 0 |
   | 1 | 0 |
   | 1 | 0 |
   | 0 | 2 |
   | 0 | 1 |
   | 1 | 0 |

No I am trying to find a sql statement that adds up column a if it's not 0 and also subtracts the value b from it. So my result should look like this

   | a | b | result |
   | 1 | 0 |   1    |
   | 0 | 1 |   0    |
   | 1 | 0 |   1    |
   | 1 | 0 |   2    |
   | 1 | 0 |   3    |
   | 0 | 2 |   1    |
   | 0 | 1 |   0    |
   | 1 | 0 |   1    |

I tried a lot with sum, count, case when, etc. but as sql noob I am not sure if those are the right functions for this problem.

CodePudding user response：

You are looking for a running total. This is usually achieved with the window function SUM OVER. You have mistakenly tagged two different DBMS, MySQL and SQLite, but both support this standard SQL functionality - unless you are using an old version.

Table data is an unordered set of data, so you need something that determines the order. I am assuming some sortkey in below query:

select sortkey, a, b, sum(a - b) over (order by sortkey) as total
from ( <your query> ) q
order by sortkey;