RaqForum 41 No.
51 View  • 
     ...

Multi combination condition grouping and aggregation

 

The table in the MS SQL database is as follows, where id is the keyword:

from

to

value

id

start_row

end_row

A

B

20

1

1

C

D

30

2

2

B

C

100

3

3

E

A

50

4

4

A

D

70

5

1

5

C

A

20

6

2

6

Now we need to first filter out records where both start_row and end_row are not null, and then calculate for each found record, finding those records in the table with IDs between start_row and end_row of this record, and where from or to is equal to the from field of this record, and then sum up the values of these records.

id

sumvals

5

140

6

150

SPL code:

　A

1

=mssql.query("select * from tb")

2

=A1.select(start_row && end_row)

3

=A2.new(id,A1.select(id>= A2.start_row && id<=A2.end_row && (from==A2.from || to==A2.from)).sum(value):sumvals)

A1: Query the database through JDBC.

A2: Filter out records where both start_row and end_row are not null, i.e. the 5th and 6th records. && indicates logic AND.

A3: Create a new two-dimensional table based on A2. The first field is taken from ID, and the calculation method for the second field is to find the records in A1 whose ID is between start_row and end_row of the current record, and whose 'from' or 'to' is equal to the 'from' of the current record. Sum up the values of these records.

Question source：https://stackoverflow.com/questions/78358979/sql-query-to-sum-some-values-from-preceeding-rows-depending-on-the-row-values


 
 •  51 View  •