Accumulation by Interval

Question

Here’s a table of two fields – a and b:
a     b
10    3
21    4
33    6
46    7
52    10
The expected result:
a<10  sum(b)
a<20  sum(b)
a<30  sum(b)
a<40  sum(b)
a<50  sum(b)

That is:
10  0
20  3
30  7
40  13
50  20
Is there any idea about how to do this in SQL? There are 1000 intervals for values of a field.

 

Answer

You can create an interval table to use with an analytic function for handling your problem. But the process is too complicated. It’s simple and easy to do it in SPL (Structured Process Language), as shown below:

A

1

$SELECT A,B FROM TB1 ORDER   BY A

2

=A1.groups((A)\10+1;sum(B)).new(#1*10:C,sum(#2[:0]):D)  

A1: Data retrieval;

A2: Group records by the intervals and sum values within the current interval to generate a new table sequence.

The final result:

 undefined