The GROUP
and GROUP BY
clauses specify how to map source records to result records in order to group statement output.
GROUP
clause maps each source record to its own result record.GROUP
maps all source records to a single result record.GROUP BY <attributeList>
maps source records to result records by the combination of values in the listed attributes.You can also use other grouping functions (such as MEMBERS
, CUBE
, or GROUPING SETS
) with the GROUP
and GROUP BY
clauses. Details on these functions are given later in this section.
BNF grammar for grouping
GROUP
and the family of group functions is:
GroupClause ::= GROUP | GROUP BY GroupByList | GROUP BY GroupAll GroupByList ::= GroupByElement | GroupByList , GroupByElement GroupByElement ::= GroupBySingle | GroupingSets | CubeRollup GroupingSets ::= GROUPING SETS (GroupingSetList) GroupingSetList ::= GroupingSetElement | GroupingSetList , GroupingSetElement GroupingSetElement ::= GroupBySingle | GroupByComposite | CubeRollup | GroupAll CubeRollup ::= {CUBE | ROLLUP} (CubeRollupList) CubeRollupList ::= CubeRollupElement | CubeRollupList , CubeRollupElement CubeRollupElement ::= GroupBySingle | GroupByComposite GroupBySingle ::= Identifier | GroupByMembers GroupByComposite ::= (GroupByCompositeList) GroupByCompositeList ::= GroupBySingle | GroupByCompositeList, GroupBySingle GroupByMembers ::= MEMBERS (Identifier | Identifier.Identifier) AS Identifier GroupAll ::= ()
GroupAll
results in the following being all equivalent:
GROUP = GROUP BY() = GROUP BY GROUPING SETS(())
Specifying only GROUP
You can use a GROUP
clause to aggregate results into a single bucket. As the BNF grammar shows, the GROUP
clause does not take an argument.
SUM
statement to return a single sum across a set of records:
RETURN ReviewCount AS SELECT SUM(NumReviews) AS NumberOfReviews FROM ProductState GROUP
This statement returns one record for NumberOfReviews. The value is the sum of the values for the NumReviews attribute.
Specifying GROUP BY
GROUP BY
to aggregate results into buckets with common values for the grouping keys. The GROUP BY
syntax is:
GROUP BY attributeList
where attributeList is a single attribute, a comma-separated list of multiple attributes, GROUPING SETS
, CUBE
, ROLLUP
, or () to specify an empty group. The empty group generates a total.Grouping is allowed on source and locally-defined attributes.
Note:
If you group by a locally-defined attribute, that attribute cannot refer to non-grouping attributes and cannot contain any aggregates. However,IN
expressions and lookup expressions are valid in this context.All grouping attributes are part of the result records. In any grouping attribute, NULL values (for single-assign attributes) or empty sets (for multi-assign attributes) are treated like any other value, which means the source record is mapped to result records. For information about user-defined NULL-value handling in EQL, see COALESCE.
{ TransId, ProductType, Amount, Year, Quarter, Region, SalesRep, Customer }
{ TransId = 1, ProductType = "Widget", Amount = 100.00, Year = 2011, Quarter = "11Q1", Region = "East", SalesRep = "J. Smith", Customer = "Customer1" }
GROUP BY
attributes, the statement results contain an aggregated record for each valid, non-empty combination of Region and Year. In EQL, this example is expressed as:
DEFINE RegionsByYear AS GROUP BY Region, Yearresulting in the aggregates of the form { Region, Year }, for example:
{ "East", "2010" } { "West", "2011" } { "East", "2011" }
GROUP BY
clauses is allowed. This means that the following two queries are treated as equivalent:
RETURN Results AS SELECT SUM(PROMO_COST) AS PR_Cost FROM SaleState GROUP BY PROMO_NAME RETURN Results AS SELECT SUM(PROMO_COST) AS PR_Cost FROM SaleState GROUP BY PROMO_NAME, PROMO_NAME
Using a GROUP BY that is an output of a SELECT expression
A GROUP BY
key can be the output of a SELECT
expression, as long as that expression itself does not contain an aggregation function.
GROUP BY
:
SELECT COALESCE(Person, 'Unknown Person') AS Person2, ... GROUP BY Person2
SUM
):
SELECT SUM(Sales) AS Sales2, ... GROUP BY Sales2