CODEWITHSUNDEEP
sqlgrailsgrails-orm
skurt
skurt

Reputation: 1019

How to do Group By in grails to order by Count(*)

How do I translate:

SELECT COUNT(*) AS `count`, `a` FROM `b` GROUP BY `a` ORDER BY `a`

into grails or gorm query?

Upvotes: 7

Views: 16066

Answers (3)

fabien7474
fabien7474

Reputation: 16562

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

Upvotes: 12

skurt
skurt

Reputation: 1019

working in grails 1.2.1

def c = C.createCriteria()
def pl = c.list {
    projections {
        countDistinct 'id', 'myCount'
        groupProperty 'a'
    }
    order ('myCount', 'desc')
}

the answer is for example

[[10,a3],[2,a1],[1,a2]]

Upvotes: 3

zentuit
zentuit

Reputation: 716

I would try

def c = b.createCriteria()
def results = c {
   projections {
      groupProperty("a")
      rowCount()
   }
   order("a")
}

Note this is untested.

Upvotes: 2

Related Questions