GROUP_BY Function
Performs summary (aggregate) functions to groups of rows in a PV‑WAVE table variable.
Usage
result = GROUP_BY(in_table, 'sum_column [alias] [ASC | DESC]')
note | The entire second parameter is a string and must be enclosed in quotes. Also, note that the vertical bar (|) means “or” in this usage. For instance, use either ASC or DESC, but not both. |
Input Parameters
in_table—An input PV‑WAVE table variable on which to perform the summary functions.
sum_column—A single column in the input table that determines how to perform the summarization. For each distinct value of sum_column in the original table, all rows that contain this value are grouped together to produce one row in the resulting table. A column with the same name and value as sum_column is created in the resulting table.
alias—Specifies a new name for sum_column in the resulting table.
ASC—Requires that the rows of the result are sorted in ascending order by the value in sum_column. If no sort order is specified, ASC is the default.
DESC—Requires that the rows of the result are sorted in descending order by the value in sum_column.
Returned Value
result—A PV‑WAVE table variable, containing one column specified by sum_column, and one for each column specified by the keywords. If the query result is empty, and no syntax or other errors occurred, the result returned is –1.
Input Keywords
For each of the following keywords, the value is a string in the format
'col1 [alias], [col2 [alias],] ... [coln [alias]]', which represents a list of column names from in_table and associated aliases for result.
Avg—Calculates the group average for each column listed in the string.
Count—Counts the number of occurrences of each data value within the group for each column listed in the string.
Max—Calculates the group maximum for each column listed in the string.
Min—Calculates the group minimum for each column listed in the string.
Sum—Calculates the group total value for each column listed in the string.
Discussion
The GROUP_BY function produces similar output to the Group By option of the QUERY_TABLE function, but GROUP_BY has a more compact and convenient syntax. For each unique value in sum_column of in_table, GROUP_BY forms a sub-table from all of the rows in in_table that have sum_column equal to that value. For each keyword specified, GROUP_BY performs the indicated function (Avg, Count, Max, Min, Sum) on each column given in the list, over all rows in the current sub-table. For each sub-table, GROUP_BY returns one row in a PV‑WAVE table variable. The row contains the current value of sum_column, and an additional column for each column in the list for each keyword.
Example
Consider the following PV‑WAVE table variable, already defined during the current session:
INFO, prop_trx
PV-WAVE prints the following:
PROP_TRX STRUCT = -> TABLE_3745584016934985140252399 Array(10000)
INFO, prop_trx, /Structure
PV-WAVE prints the following:
** Structure TABLE_3745584016934985140252399, 8 tags, 72 length:
TRX_ID LONG 0
PROP_TYPE STRING 'OTHER '
PROP_ADDRESS STRING ''
PROP_POST_CD STRING ''
PROP_XGRID DOUBLE 0.0075200000
PROP_YGRID DOUBLE 1.6357100
TRX_AMT DOUBLE 116383.00
TRX_DATE STRUCT -> !DT Array(1)
Suppose that we would like to find the total amount, average amount, count, average x grid value, and average y grid value for each property type. We could accomplish this with the following call to GROUP_BY:
trx_sum = GROUP_BY( prop_trx, 'prop_type my_prop_type ', $
AVG='trx_amt my_avg_amt, prop_xgrid my_avg_x, ' + $
'prop_ygrid my_avg_y ', SUM='trx_amt my_total_amt', $
COUNT='prop_type my_type_cnt')
We would get a new PV‑WAVE table, trx_sum, which has 9 rows (one for each unique value of prop_type).
INFO, trx_sum
PV-WAVE prints the following:
TRX_SUM STRUCT = -> TABLE_2654125490145392573020051 Array(9)
The columns in trx_sum are as follows:
INFO, trx_sum, /Structure
PV-WAVE prints the following:
** Structure TABLE_2654125490145392573020051, 6 tags, 48 length:
MY_PROP_TYPE STRING 'STUDIO '
MY_AVG_AMT DOUBLE 54541.422
MY_AVG_X DOUBLE 2.5688594
MY_AVG_Y DOUBLE 1.5601237
MY_TYPE_CNT LONG 1075
MY_TOTAL_AMT DOUBLE 58632029.
Note that this could also be accomplished with the following call to QUERY_TABLE:
trx_sum2 = QUERY_TABLE( prop_trx, 'prop_type my_prop_type, ' + $
'AVG(trx_amt) my_avg_amt, AVG(prop_xgrid) my_avg_x, ' + $
'AVG(prop_ygrid) my_avg_y, SUM(trx_amt) my_total_amt, ' + $
'COUNT(prop_type) my_type_cnt GROUP BY prop_type ')
which produces the following results:
INFO, trx_sum2
PV-WAVE prints the following:
TRX_SUM2 STRUCT = -> TABLE_7241171353020130317830120 Array(9)
INFO, trx_sum2, /Structure
PV-WAVE prints the following:
** Structure TABLE_7241171353020130317830120, 6 tags, 48 length:
MY_PROP_TYPE STRING 'STUDIO '
MY_AVG_AMT DOUBLE 54541.422
MY_AVG_X DOUBLE 2.5688594
MY_AVG_Y DOUBLE 1.5601237
MY_TYPE_CNT LONG 1075
MY_TOTAL_AMT DOUBLE 58632029.
See Also
For more information on BUILD_TABLE, see PV‑WAVE User’s Guide.
For information on reading data into variables, see PV‑WAVE Programmer’s Guide.
