1.16 Grouping: get groups according to the specified aggregate value
Based on the following daily product sales table, find dates when daily sales amount exceeds 300,000.
| OrderDate | ProductName | Sales |
|---|---|---|
| 1996-07-04 | Aniseed Syrup | 51919.0000 |
| 1996-07-04 | Chai | 21169.0000 |
| 1996-07-04 | Chang | 23154.0000 |
| 1996-07-04 | Chef Anton’s Cajun Seasoning | 20307.0000 |
| 1996-07-04 | Chef Anton’s Gumbo Mix | 97636.0000 |
| … | … | … |
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query@x(“select * from ProductDailySales”) |
| 3 | =A2.groups(OrderDate;sum(Sales):TotalSales) |
| 4 | =A3.select(TotalSales>300000) |
A1 Connect to the database;
A2 Query ProductDailySales table;
A3 Group A2’s records by order date and sum sales amounts in each group;
A4 Get records where the daily total sales amount exceeds 300,000.
Execution result:
| OrderDate | TotalSales |
|---|---|
| 1996/7/10 | 394565 |
| 1996/7/11 | 402883 |
| 1996/7/22 | 331978 |
| 1996/8/1 | 304470 |
| 1996/8/6 | 306196 |
| … | … |
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
