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.