CODEWITHSUNDEEP
grailshql
Michael
Michael

Reputation: 33297

Count the number of groups in HQL Query?

I have the following HQL query:

select c.device from Choice c 
group by c.device

Now I want to count the number of groups in the result not the number of devices per group.

I tried: 

select count(distinct c.device) from Choice c 
    group by c.device

but this give the number of distinct devices in each group. This is something like [2,3,4]. But I need 2+3+4.

How do I get the number of groups with HQL?

Upvotes: 0

Views: 1080

Answers (1)

Emmanuel Rosa
Emmanuel Rosa

Reputation: 9885

You would have to do a count of a count, which is not supported in HQL.

SQL

In SQL it would look something like this:

select count(innerQuery.counted)
from (select count(d.id) as counted
      from Choice c inner join Device d
      on c.device_id = d.id
      group by d.id) as innerQuery

In the example above the outer query selects from a sub-query which returns the groups. The outer query then does a count on the generated counted column.

HQL

An alternative is to do a count and then get the size of the list.

Choice.executeQuery('select count(c.device) from Choice c group by c.device').size()

There's a performance penalty because the counting is done on the client-side.

Upvotes: 2

Related Questions