group - Group operands
GROUP operands define one or more grouping attributes for grouping instances in an operand list (dimensions). By default, dimensions also define the order key for grouping instances. GROUP operands should define attributes and are passed to the GROUP method.
Operands in the operand list may be attribute names or property paths referring to attributes defined in the FROM data type (e.g. (age, sex) ) or the data type of calling object. Grouping attributes usually refer to elementary data types, but may also refer to complex data types or user-defined enumerations.
By preceding the attribute references with names (e.g. (age_group = age), one may explicitly assign attribute names to grouping attributes.
When the data source is an operation, an attribute name has to be defined explicitly (e.g. ( string age_group = AgeGroup() ) ). The operation must be a valid expression in the context of the calling object (usually the FROM data type). One may omit the type definition, when the operation is defined as OSI function in the resource data base or in a preloaded OSI function, which provides the type in the return value definition.
In order to call grouping functions in an operation path, group (or GROUP) should be used instead of GROUP BY.
Each GROUP instance contains grouping attributes (dimensions) and a collection partition containing all instances from the FROM data source matching the grouping attributes. The partition property becomes the default source for aggregation functions.
In case of GROUP definition in a view definition or SELECT statement, source operands for view members (SELECT properties) refer to the GROUP data type. Aggregation functions without calling object refer to partition, i.e. property names passed to aggregation functions must be defined in the data type for partition instances.
// SELECT statement
SELECT(age_group, inc_group, sex, inc = sum(income),
avr_inc = average(income) )
FROM ( Persons )
GROUP BY (sex,
string inc_group = (income < 1000 ? 'poor' :
income < 5000 ? 'medium':
income < 100000 ? 'rich':
'very rich' ),
string age_group = (age < 20 ? 'young' :
age < 50 ? 'middle':
'old' ) );
// call group in an OSI operation path
from(Persons).group(sex,
inc_group = (income < 1000 ? 'poor' :
income < 5000 ? 'medium':
income < 100000 ? 'rich':
'very rich' ),
age_group = (age < 20 ? 'young' :
age < 50 ? 'middle':
'old' ) ).
select( age_group, inc_group, sex, inc = sum(income),
avr_inc = average(income));
// call group in a C++/Java/C# program
from("Persons").group("sex,
inc_group = (income < 1000 ? 'poor' :
income < 5000 ? 'medium':
income < 100000 ? 'rich':
'very rich' ),
age_group = (age < 20 ? 'young' :
age < 50 ? 'middle':
'old' )").
select("age_group, inc_group, sex, inc = sum(income),
avr_inc = average(income)");