Menu
How do I translate:
SELECT COUNT(*) AS `count`, `a` FROM `b` GROUP BY `a` ORDER BY `a`
into grails or gorm query?
833
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
In the query, GROUP BY clause is placed after the WHERE clause. In the query, GROUP BY clause is placed before ORDER BY clause if used any.
Using COUNT, without GROUP BY clause will return a total count of a number of rows present in the table. Adding GROUP BY, we can COUNT total occurrences for each unique value present in the column.
Since grails 1.2 you can create aliases and order by the created alias.
See https://cvs.codehaus.org/browse/GRAILS-3875 and https://cvs.codehaus.org/browse/GRAILS-3655 for more details.
Applied to your own code, the HQL query would be :
def c = b.createCriteria()
def results = c {
projections {
groupProperty("a")
count("a", 'myCount') //Implicit alias is created here !
}
order 'myCount'
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
