GROUP BY

Group a result into subsets that have matching values for one or more columns.

Syntax

GROUP BY column-name [ , column-name ] *

Description

A GROUP BY clause, part of a SelectExpression, groups a result into subsets that have matching values for one or more columns. In each group, no two rows have the same value for the grouping column or columns. NULLs are considered equivalent for grouping purposes.

You typically use a GROUP BY clause in conjunction with an aggregate expression.

column-name must be a column from the current scope of the query; there can be no columns from a query block outside the current scope. For example, if a GROUP BY clause is in a subquery, it cannot refer to columns in the outer query.

GemFire XD does not support using GROUP BY on a column of datatype BLOB, CLOB, or LONG VARCHAR FOR BIT DATA.

SelectItems in the SelectExpression with a GROUP BY clause must contain only aggregates or grouping columns.

Example

-- find the quantity of units held by customer  grouped by customer ID
SELECT AVG (QTY), CID FROM TRADE.PORTFOLIO GROUP BY CID
SELECT MAX(F.QTY), C.CID FROM TRADE.PORTFOLIO F, TRADE.CUSTOMERS C
WHERE F.CID = C.CID GROUP BY C.CID
SELECT (AVG(SUBTOTAL/QTY)) FROM TRADE.PORTFOLIO F WHERE F.SID = ? AND F.TID =? AND F.QTY <> 0