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.

This allows you to use any of the standard group functions that are applicable, such as those used to calculate minimum, maximum, count, sum, average.

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 as follows: 

select c.zipcode from customer c group by c.zipcode;

Note that, although the group by clause reduces the result set to a list—in this example to a list of zip codes—additional information from the query is internally available to the aggregation functions.

Group By Usage

The select clause can use only the group by criteria and aggregation functions.

For example, the following example is valid: 

select s.deptName, count(*)
  from /Student s
  group by s.deptName

However, the following example is invalid: 

INVALID  select s.deptName, s.deptNo, count(*)
           from /Student s
           group by s.deptName

In the second example, s.deptNo does not appear in the group by clause and therefore it cannot be used in the select clause.

Using a Dummy Group Expression for Aggregation

Suppose you want to get a count of all entities in the from clause. In this case you must use a group by clause that creates a dummy group. In this case, all the rows are in the same group. As an example: 

select count(*)
  from /Student s
  group by 1

The group by clause restricts the columns that can be used in the select clause. So, as an example, this usage is invalid: 

INVALID  select s.deptName, count(*)
           from /Student s
           group by 1

Dummy groups are created when you specify a constant in the group by clause. For example, you can specify a dummy group in any of the following ways: 

group by ""
group by 1
group by 2
group by "hello"

Any constant can be used.

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.