GROUPBY macro

The GROUPBY macro is available only in IBM® Campaign.

GROUPBY(group_field, keyword, rolled_field [,output_field])

Specifies the variable over which records are grouped (that is, all the same values of the specified variable are grouped together).

keyword

Specifies the summary roll-up function to perform on the rolled-field.

rolled_field

Specifies the variable to be summarized or rolled up.

output_field

Identifies an alternate variable to be returned for a single row of a group and can be used only with the keywords MinOf, MaxOf, and MedianOf.

Description

GROUPBY summarizes across multiple rows of data within a group. The output of this function is a single column. The output is the result of the operation specified by keyword on the rolled_field over the homogeneous group specified by the group_field. If there is more than one answer satisfying a specified condition, the first one encountered is returned.

If the optional output_field is not supplied, then the output is the result of the operation on rolled_field. If output_field is supplied, then the result is the output_field of the row within the group.

If there are multiple rows within a group that satisfy the specified condition (for example, there are ties for the max value), the output-field associated with the first row satisfying the condition is returned.

To work with grouping over multiple columns, you can enclose a list of field names, separated by commas, within a set of "curly" brackets "{ }" and using this as the first parameter in the GROUPBY macro call.

Supported keywords are as follows (case insensitive):

Keyword	String? Yes/No	Description
CountOf	Yes	Returns the number of records in each group (rolled_field can be numeric or string; the returned value is the same regardless of the value of rolled_field).
MinOf	Yes	Returns the minimum value of rolled_field in each group (rolled_field can be numeric or string; if rolled_field is a string, the value closest to the beginning of the alphabet where alphabetically sorted is returned).
MaxOf	Yes	Returns the maximum value of rolled_field in each group (rolled_field can be numeric or string; if rolled_field is a string, the value closest to the end of the alphabet when alphabetically sorted is returned).
DiffOf	Yes	Returns the number of distinct values of rolled_field in each group (rolled_field can be numeric or string).
AvgOf	No	Returns the average value of rolled_field in each group (rolled_field must be numeric).
ModeOf	Yes	Returns the modal value (that is, the most commonly occurring value) of rolled_field in each group (rolled_field can be numeric or string).
MedianOf	Yes	Returns the median value (that is, the middle value when sorted by rolled_field) of rolled_field in each group (rolled_field can be numeric or string; if rolled_field is a string, the values are sorted alphabetically).
OrderOf	Yes	Returns the order of rolled_field in each group (rolled_field must be numeric). If multiple records have the same value, they all receive the same value.
SumOf	No	Returns the sum of rolled_field in each group (rolled_field must be numeric).
StdevOf	No	Returns the standard deviation of rolled_field in each group (rolled_field must be numeric).
IndexOf	Yes	Returns the 1-based index (ordered by rolled_field) of each record (rolled_field can be numeric or string). The sort order is ascending. Note: For numeric fields, the sort order of RankOf and IndexOf can be made descending by putting a minus sign (-) in front of the sort field.
RankOf	Yes	Returns the 1-based category (ordered by rolled_field) in which each record lies (rolled_field can be numeric or string). The sort order is ascending. Note: For numeric fields, the sort order of RankOf and IndexOf can be made descending by putting a minus sign (-) in front of the sort field.

Examples

GROUPBY (Household_ID, SumOf, Account_Balance)

Computes the sum of all account balances by household.

GROUPBY (Cust_ID, MinOf, Date(Account_Open_Date), Acc_Num)

Returns the account number of first account opened by customer.