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

NOTE: 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
PROP_TRX        STRUCT    = - TABLE_3745584016934985140252399 Array(10000)

INFO, prop_trx, /Structure
** 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
TRX_SUM         STRUCT    = - TABLE_2654125490145392573020051 Array(9)

The columns in trx_sum are as follows:


INFO, trx_sum, /Structure
** 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
TRX_SUM2        STRUCT    = - TABLE_7241171353020130317830120 Array(9)

INFO, trx_sum2, /Structure
** 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.

GROUP_BY Function

Usage

Input Parameters

Returned Value

Input Keywords

Discussion

Example

See Also