Sum Every 5 Rows
【Question】
I have a table tb1:
SL
5
7
7
8
8
5
7
8
2
3
4
2
The expected result:
1-5 35
6-10 25
11-12 6
【Answer】
You need to create sequence numbers for each record before performing sums because SQL is lack of ordered sets. That’s a hassle. A simple way is handling it in SPL (Structured Process Language) that supports ordered sets.
| A |
|
| 1 |
$select SL from tb1 |
| 2 |
=A1.groups((#-1)\5:GN;sum(SL):SL) |
| 3 |
=A2.run(GN=string(1+GN\*5)+"-"+string(min(A1.len(),5+GN\*5))) |
A3 returns the final result:
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
