Sum a Specific Column and Sort Result by a Specified Column
Problem description & analysis
Database table SAMPLE has three fields: DATE, VALUE and SID, as shown below:
| DATE |
VALUE |
SID |
| 2000-01-01 |
1 |
2 |
| 2000-01-02 |
2 |
3 |
| 2000-01-03 |
3 |
4 |
| 2000-01-04 |
4 |
1 |
DATE values are already ordered. We are trying to sum VALUE values cumulatively, name the new aggregate column CUMULATIVEVALUE, and sort records by SID. Below is the desired result:
| DATE |
VALUE |
SID |
CUMULATIVEVALUE |
| 2000-01-04 |
4 |
1 |
10 |
| 2000-01-01 |
1 |
2 |
1 |
| 2000-01-02 |
2 |
3 |
3 |
| 2000-01-03 |
3 |
4 |
6 |
Solution
We write the following script p1.dfx in esProc:
| A |
|
| 1 |
=connect("demo") |
| 2 |
=A1.query@x("SELECT * FROM SAMPLE ORDER BY DATE") |
| 3 |
=A2.derive(VALUE+CUMULATIVEVALUE[-1]:CUMULATIVEVALUE).sort(SID) |
Explanation:
A1 Connect to demo database.
A2 Execute SQL, return query result as a table sequence, and close database connection automatically.
A3 Add a computed column CUMULATIVEVALUE to A2’s table sequence. Each new value is the current VALUE plus the previous CUMLATIVEVALUE. Then sort the result records by SID.
We can also write A3 as follows:
=A2.derive(iterate(~~+VALUE):CUMULATIVEVALUE).sort(SID)
Read How to Call an SPL Script in BIRT to learn about the method of integrating the SPL script into BIRT.
https://www.eclipse.org/forums/index.php/t/171708/
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
