Returns a table showing distinct groups of values found in one or more columns. An extra column is appended with the sum of the specified sum column values, in rows, per distinct group.
SppTableGroupByColumnsWithSum([Table Array],[Group By Columns],[Sum Column])
Where:
Table Array Table Array is a Table Array (such as the data in a standard table, or the result of a QueryDataValues function.
Group By Columns is the indexes of the columns to group by as a pipebar (|) delimited string, for example 2|7|5. Number starting at 1 for the first column.
Sum Column is the column in the Table Array that will be summed for each unique value combination of Group By Columns. Number starting at 1 for the first column.
| Rule | Result | Meaning |
|---|---|---|
| SppTableGroupByColumnsWithSum(DwLookupCasingOrders,"1|4",3) | {"Part","Primary Color","Sum(Quantity)"; "Casing","Red",40; "Frame","Black",55; "Chassis","Blue",40} | Will return an Array showing distinct values from the Part (first) column and Primary Color (fourth) column, and append the Sum Quantity (third) column. |
See Example Result Viewed as a Table below to see how the result of this example looks when viewed as a table.
The data below exists as a Table in DriveWorks named CasingOrders (DwLookupCasingOrders)
| Part | Price | Quantity | Primary Color | Secondary Color |
|---|---|---|---|---|
| Casing | 210 | 15 | Red | Red |
| Casing | 200 | 25 | Red | Blue |
| Frame | 250 | 5 | Black | Orange |
| Chassis | 195 | 30 | Blue | Teal |
| Chassis | 230 | 10 | Blue | Blue |
| Frame | 190 | 50 | Black | Black |
The array returned by the rule SppTableGroupByColumnsWithSum(DwLookupCasingOrders,"1|4",3) will look like:
| Part | Primary Color | Sum(Quantity) |
| Casing | Red | 40 |
| Frame | Black | 55 |
| Chassis | Blue | 40 |