6.2 Ordinary grouping: by expression
We have the following data:
| ID | Name | Sales |
|---|---|---|
| 010010001 | Tom | 7,588,420 |
| 010010002 | John | 8,953,172 |
| 010010003 | Joan | 6,276,185 |
| 020010004 | Rocky | 1,281,280 |
| 020010005 | Ham | 2,686,043 |
| 020010006 | Kate | 5,851,210 |
| 020010007 | Rose | 292,254 |
| 030020008 | Nomy | 8,216,267 |
| 030020009 | Neil | 5,143,192 |
| 030020010 | Jack | 3,206,181 |
| 030020011 | Joe | 5,536,126 |
| 030020012 | Peter | 6,176,479 |
| 040020013 | Sunny | 7,319,085 |
| 040020014 | Tiger | 2,392,104 |
| 040020015 | Alice | 446,324 |
| 040020016 | Cindy | 5,137,729 |
| 040020017 | Leon | 9,663,806 |
| 050030018 | Lily | 4,265,516 |
| 050030019 | Kevin | 8,447,330 |
| 050030020 | Shelly | 5,781,475 |
| 050030021 | Panzy | 1,917,614 |
| 050030022 | Maggie | 3,979,820 |
| 050030023 | Mark | 4,250,158 |
| 050030024 | Aileen | 8,215,770 |
The computing target is to group data by the first two bits of ID field and calculate total sales amount on each group.
SPL script:
| A | |
|---|---|
| 1 | =T(“Sales.xls”) |
| 2 | =A1.groups(left(ID,2):ID;sum(Sales):Sales) |
| 3 | =A1.group(left(ID,2):ID;~.sum(Sales):Sales) |
A2 Group A1’s table while performing sum aggregation without generating grouped subsets.
A3 Generate grouped subsets and then perform sum aggregation on each subset; this gets same result as A2.
Execution result:
| ID | Sales |
|---|---|
| 01 | 22,817,777 |
| 02 | 10,110,787 |
| 03 | 28,278,245 |
| 04 | 24,959,048 |
| 05 | 36,857,683 |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
