Declarative Objectivity (DO) Language : Data Clauses : HAVING Clause
HAVING Clause
A HAVING clause filters results in a GROUP BY Clause using a specified criterion.
Syntax 
havingClause : HAVING expression;
Used In 
RETURN Statement, CREATE Statement, UPDATE Statement, DELETE Statement
Quick Look 
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;
Discussion 
A HAVING clause qualifies the groups of objects established by the GROUP BY clause, filtering them based on some quality evaluated for each group. For example, the following statement filters out groups that consist of fewer than 20 results:
FROM Vehicles GROUP BY model.brand HAVING COUNT() > 20 RETURN model.brand, COUNT() as count;
Expression in the HAVING Clause
When a statement includes a GROUP BY clause, the expression in the HAVING 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 expression is not restricted to such attributes.
Note that the expression in the HAVING 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 the expression consists of non-grouping attributes used with ordinary 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 customers by postal code, finds the sum of the reward points for the customers in each group, and returns only the groups whose total reward points is less than than 2000:
FROM Customers GROUP BY address.postalCode HAVING SUM(rewardPoints) < 2000 RETURN address.postalCode, SUM(rewardPoints) as 'total points', COUNT() as count;
In the above statement, address.postalCode is a grouping attribute, while rewardPoints is not. The expression that uses the group aggregate operator SUM() takes every Customer object’s rewardPoints attribute into account, even though rewardPoints is not a grouping attribute. In effect, the above statement returns the regions in which combined customer activity is below a certain threshold.
Now consider the following statement, in which the expression in the HAVING clause consists of an ordinary (non-aggregate) relational operator (<) with the non-grouping attribute rewardPoints:
FROM Customers GROUP BY address.postalCode HAVING rewardPoints < 2000 RETURN address.postalCode, SUM(rewardPoints) as 'total points', COUNT() as count;
The above statement returns only the groups whose first Customer object has a rewardPoints amount that is less than 2000. This is not a particularly useful result, because the order in which objects in a result group are processed is undefined (dependent on the data store) and could change if more Customer objects are created.
If you were interested in the total reward points of just the customers with the lowest activity in each region, you could use a WHERE Clause instead of a HAVING clause to qualify the customers that are put into groups in the first place:
FROM Customers where rewardPoints < 200 GROUP BY address.postalCode RETURN address.postalCode, SUM(rewardPoints) as 'total points', COUNT() as count;
The WHERE clause filters the input to a GROUP BY clause, while the HAVING clause filters the GROUP BY clause’s output.