Grails HQL Group By Examples

Mar 08, 2015 Snippet comments

This post will show how to perform HQL Group By in your Grails applications.

Examples

Similar to SQL, HQL also supports Group By Clause. If we have this domain class:

package asia.grails.test
class Person {
    String firstName
    String lastName
    int age
}

And we have some data. We can use group by clause to group data and use aggregate function such as count. Here is an example:

def list = Person.executeQuery("select age, count(*) from Person group by age")
list.each { item ->
    def age = item[0]
    def count = item[1]
    println "There are ${count} people with age ${age} years old"
}

This will print all ages found in the table and how many people have that age. This is a sample output of the code on the console:

There are 5 people with age 1 years old
There are 3 people with age 2 years old
There are 10 people with age 3 years old

Having clause

The having clause is useful to filter out the result of a group by. Here is an example:

def list = Person.executeQuery(
    "select age, count(*) from Person group by age having count(*) > 1")
list.each { item ->
    def age = item[0]
    def count = item[1]
    println "There are ${count} people with age ${age} years old"
}

This will print all ages found in the table and how many people have that age, provided that there are more than 1 person in the age group.

If you are new to SQL or HQL, the where and having clause have a huge difference in behavior. The where clause is executed before data is grouped, whereas the having clause is executed after data is grouped. Hence we can only use aggregate functions in the having clause