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
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