Grails Cookbook - A collection of tutorials and examples

Grails HQL Count Examples

This post will show how to use the COUNT aggregate function inside Grails/GORM HQL statements. Just like in SQL, HQL COUNT statements returns the number of rows that matches a specified criteria.

HQL Count

Here is an example domain and test data to work with

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

class BootStrap {
    def init = { servletContext ->
        if (Person.count()==0) {
            new Person(firstName: 'John', lastName: 'Doe', age: 25).save()
            new Person(firstName: 'Jane', lastName: 'Doe', age: 30).save()
            new Person(firstName: 'Jesse', lastName: 'Doe', age: 20).save()
            new Person(firstName: 'Rex', lastName: 'Smith', age: 12).save()
            new Person(firstName: 'Roy', lastName: 'Smith', age: 18).save()
            new Person(firstName: 'Amy', lastName: 'Johnson', age: 8).save()
        }
    }
    def destroy = {
    }
}

Count Everything

def result = Person.executeQuery("select count(*) from Person")
def resultCount = result[0]
println "There are ${resultCount} records in the person table"

This will output "There are 6 records in the person table".

Count with Where Clause

How many people have surname Doe?

def result = Person.executeQuery(
    "select count(*) from Person where lastName = 'Doe'")
def resultCount = result[0]
println "There are ${resultCount} people with surname Doe"

This will output "There are 3 people with surname Doe"

How many people are at least 10 years old?

def result = Person.executeQuery(
    "select count(*) from Person where age >= 10")
def resultCount = result[0]
println "There are ${resultCount} people who are at least 10yo"

This will output "There are 5 people who are at least 10yo"


Group By

We can use count together with a group by clause. For example:

How many people have the same surnames?

def result = Person.executeQuery(
    "select lastName, count(*) from Person group by lastName")
result.each { item ->
    def lastName = item[0]
    def lastNameCount = item[1]
    println "There are ${lastNameCount} that have the surname ${lastName}"
}

The code will output:

There are 3 that have the surname Doe
There are 1 that have the surname Johnson
There are 2 that have the surname Smith

Remarks

The examples shown above are simple use cases for HQL count statements. I encourage the readers to experiment on more complex examples.