Chapter 6 Query Language Components : Group by Clause

Group by Clause
The optional group by clause allows you to group entities that share one or more criteria into a single row—each group is represented by one row.
When you use a group by clause, the select clause cannot use the entities specified in the from clause  What is available to the select clause has been, in effect, reduced using the group by clause. When you use a group by clause, the select clause can use only the group by criteria, and aggregation functions.
For example:
select c.zipcode from customer c group by c.zipcode;
The above query would return a list of zip codes.
Although the group by clause in this example reduces the result set to a list of zip codes, additional information from the query is internally available to the aggregation functions. You can use any of the standard group functions that are applicable, such as those used to calculate count, sum, average, maximum, and minimum.
Aggregation functions operate on all entities (and their attributes and properties) that make up a given group. For example, you could find out how many customers are in each zip code:
select count(*) from customer c group by c.zipcode;
Optional having Clause
The optional having clause allows you to apply conditions after entities are grouped. For example this query returns the number of customers in each zip code, except for those zip codes where there are three or fewer customers:
select c.zipcode, count(*) as count_zipcode
from /customer c
group by c.zipcode
having count_zipcode > 3;
Note that the having clause accepts aliases declared in the select clause.
You can also use aggregation functions in the having clause in order to apply conditions on the whole group.