Returns a table showing distinct groups of values found in one or more columns. An extra column is appended with the average of the specified column values, in rows, per distinct group.
SppTableGroupByColumnsWithAverage([Table Array],[Group By Columns],[Average 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.
Average Column is the column in the Table Array that will be averaged for each unique value combination of Group By Columns. Number starting at 1 for the first column.
| Rule | Result | Meaning |
|---|---|---|
| SppTableGroupByColumnsWithAverage(DwLookupCasingOrders,"1|4",2) | {"Part","Primary Color","Average(Price)"; "Casing","Red",205; "Frame","Black",220; "Chassis","Blue",212.5} | Will return an Array showing distinct values from the Part (first) column and Primary Color (fourth) column, and append the Average Price (second) 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 SppTableGroupByColumnsWithAverage(DwLookupCasingOrders,"1|4",2) will look like:
| Part | Primary Color | Average(Price) |
|---|---|---|
| Casing | Red | 205 |
| Frame | Black | 220 |
| Chassis | Blue | 212.5 |