Declarative Objectivity (DO) Language : Data Clauses : GROUP BY Clause
GROUP BY Clause
A GROUP BY clause groups returned objects according to common values, optionally filtering out groups that do not meet the conditions specified by a HAVING Clause.
Syntax 
groupByClause : GROUP BY expression (',' expression)* havingClause?;
Used In 
RETURN Statement, CREATE Statement, UPDATE Statement, DELETE Statement
Quick Look 
Returns the number of vehicles made by the same manufacturer:
FROM Vehicles GROUP BY model.brand RETURN model.brand, COUNT() as count;
Returns the names of manufacturers that made more than 20 cars in the fleet:
FROM Vehicles GROUP BY model.brand HAVING COUNT() > 20 RETURN model.brand, COUNT() as count;
Returns the number of reservations made per year:
FROM Reservations GROUP BY YEAR(timestamp) RETURN YEAR(timestamp) as year, COUNT() as count;
  
Discussion 
A GROUP BY clause creates temporary groups from what otherwise would be individual returned objects. The usual purpose is to find out whether groups of objects have values in common, and, if so, to investigate some quality of those groups, typically using an aggregate operator.
You specify a common value using an expression that typically designates a reachable value, such as:
A direct or indirect attribute of the current context object.
One or more elements of a list or a map that is directly or indirectly related to the current context object.
The expression must return a simple value, such as a Boolean, numeric, string, date, time, or datetime value.
For example, the following statement subdivides the vehicles into groups according to string name of the manufacturer, and then counts the number of vehicles in each group.
FROM Vehicles GROUP BY model.brand RETURN model.brand, COUNT() as count;
You can specify multiple criteria on which to base the temporary groups. For example, the following query subdivides customers according to combinations of country and state attributes. The result could be used as a sales report showing the average and total sales to customers within each geographical region:
FROM Customers GROUP BY country, state RETURN country, state, AVG(sales) AS 'average sales', SUM(sales) AS 'total sales';
Results can be further qualified by a HAVING Clause.
Expression in the RETURN Clause
When a statement includes a GROUP BY clause, the expression in the RETURN clause normally includes one or more grouping attributes (attributes from the GROUP BY clause), because those attributes show the common values on which each group is based. However, the RETURN expression is not restricted to such attributes.
Note that the expression in the RETURN clause is evaluated differently depending on its contents. In particular, the expression is evaluated against every object in a result group if that expression consists of some combination of the following:
Grouping attributes that are also present in the statement’s GROUP BY clause.
An aggregate operator, such as COUNT() or SUM(), which may, but need not, have grouping attributes as operands.
Otherwise, if an expression consists of non-grouping attributes used with ordinary operators (or with no operators), it is evaluated only against the first object in a group (and not against all objects in the group).
To illustrate these rules, consider the following statement, which groups the vehicles by model name and displays the maximum seating capacity for the vehicles within each group.
FROM Vehicles GROUP BY model.brand RETURN model.brand, MAX(model.seatingCapacity) AS 'max capacity', COUNT() as count;
The expression that uses the aggregate operator MAX() considers every vehicle in each group to discover the largest capacity, even though seatingCapacity is not a grouping attribute.
Now consider the following statement, in which an expression in the RETURN clause uses the seatingCapacity attribute with the arithmetic operator minus:
FROM Vehicles GROUP BY model.modelName RETURN model.modelName, model.seatingCapacity - 1 AS 'not very useful', COUNT() as count;
The usefulness of this expression is not so clear. Because seatingCapacity is a non-grouping attribute used with an ordinary operator, this expression returns just the decremented seating capacity of the first vehicle in each group. Even if that were a useful result, it’s not necessarily a consistent one, because the order in which the objects in a result group are processed is undefined (dependent on the data store) and could change if more Vehicle objects are created.
See also Expression in the HAVING Clause.