sql construction

Question

Problem like this: table A contain one column number in for recording income, table B contain one column number out for recording outcome.

maybe at an instance, table A records 3,12,3,25,1,3 and table B records 23,1,4. You can consider that I got money 3,12,3,25,1,3 and spent 23,1,4.

Everytime I spends money will take number ordinally 3 first and than 12 util the need is met(3,12,3,25 in this example for 3+12+3+25>23+1+4>3+12+3)

What I want to konw is how can I construct a sql to query what income left? in example above, I shall get 25,1,3 left, yes 25 included also.

// solution in java code level but no elegant
private List<Integer> example(List<Integer> aList, List<Integer> bList){
    int sumB = bList.stream().reduce(0, Integer::sum);
    List<Integer> res = new ArrayList<>();

    int sumA = 0;
    for (int a: aList){
        sumA = sumA + a;
        if (sumA>sumB) res.add(a);
    }
    return res;
}

What I have tried is to fetch all data and compare those two columns in java code level, it works but not elegant, — TomX, Oct 27 '17 at 13:28
So please post some code example and explain us why it's not elegant / how can be improved — T30, Oct 27 '17 at 13:29
I just want to get the right result in sql level, but i don't konw how to construct this kind of sql — TomX, Oct 27 '17 at 13:29
This doesn't sound a simple task in standalone sql. Some sort of iterator can do it but it's hundred time more simple to process your results in java. — T30, Oct 27 '17 at 13:31
In my view, db must have some simple ways to process data fast than code, but i don't how to do it — TomX, Oct 27 '17 at 13:39
Fast is often enemy of simple. If it's not a critical real-time intensive application, I wouldn't mind some millisecond of overehead. Moreover, sql is for storing and retreiving data, more than processing them. — T30, Oct 27 '17 at 13:42
Please **[EDIT]** your question and add some [sample data](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. [**Formatted text**](http://stackoverflow.com/help/formatting) please, [**no screen shots**](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). See e.g. [here](https://stackoverflow.com/q/46976323/330315) or [here](https://stackoverflow.com/q/46974507/330315) or [here](https://stackoverflow.com/q/46966462/330315) for questions with good sample data — , Oct 27 '17 at 13:54
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? Postgres? Oracle? "_SQL_" is just a query language, not the name of a specific database product. — , Oct 27 '17 at 13:54

LukStorms · Accepted Answer · 2017-10-27T15:14:22.713

1

If your RDBMS supports SQL window functions then you could calculate a cumulative sum for Table A, and compare that against the total amount of Table B.

select amount
from (
  select id, amount, sum(amount) over (order by id) cummulativeSumA 
  from TableA
) as A
cross join (
  select sum(amount) as totalB from TableB
) as B
where A.cummulativeSumA > B.totalB;

edited Oct 27 '17 at 15:14

answered Oct 27 '17 at 14:02

LukStorms

28,916
5
31
45

I use mysql , it seems not support over keyword – TomX Oct 27 '17 at 14:18
Then I assume your MySql version is older than 8.0.2. Because I thought [they did introduce them to MySql at some point](http://mysqlserverteam.com/mysql-8-0-2-introducing-window-functions/). – LukStorms Oct 27 '17 at 14:32
Could have a look at this old [post](https://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql) for inspiration how to get a cumulative sum in a MySql that can't use window functions. – LukStorms Oct 27 '17 at 14:42

sql construction

1 Answers1