9.17.6 GROUP BY clause and HAVING clause
With the GROUP BY clause, the query results are compiled for each group. With the HAVING clause, you can specify conditions for narrowing down the query results further. After specifying the group, specify the HAVING clause conditions.
If a query contains both the WHERE clause and GROUP BY clause, the WHERE clause is executed first, the format is adjusted with the GROUP BY clause, and then filtering is performed according to the HAVING clause.
The items other than the set function appearing in the SELECT clause must also be specified in the GROUP BY clause. With grouping, the null value is also included and is handled as a condition. The notes related to the GROUP BY clause and HAVING clause are as follows:
-
Entity-based grouping can be performed, but serialized fields and lob fields cannot be included. If serialized fields and lob fields are specified with Cosminexus JPA Provider, an exception occurs.
-
In the HAVING clause, search conditions are specified for the group items, so the set function applicable to the group items must be specified. With Cosminexus JPA Provider, if the search conditions are not specified, an exception occurs.
-
Do not use the HAVING clause when the GROUP BY clause does not exist. If used, an exception occurs.
An example of coding the GROUP BY clause and HAVING clause is as follows:
SELECT e.department.departmentId FROM Employee AS e GROUP BY e.department.departmentId HAVING COUNT(e.department.departmentId) <= 2
For details on the syntax of the GROUP BY clause and HAVING clause, see Appendix G BNF for JPQL.