GROUPBY macro
The GROUPBY
macro
is available only in Unica Campaign.
Syntax
GROUPBY(group_field, keyword,
rolled_field [,output_field])
Parameters

group_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 rollup function to perform on the rolledfield.

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
, andMedianOf
.
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 outputfield
associated
with the first row satisfying the condition is returned.
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 1based 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 1based 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. 