0

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?

doelleri
  • 19,232
  • 5
  • 61
  • 65
Michael
  • 32,527
  • 49
  • 210
  • 370
  • Looks like [sum](http://docs.jboss.org/hibernate/orm/5.0/userGuide/en-US/html_single/#d5e2074) might do what you want? – doelleri Sep 28 '15 at 18:46
  • 1
    Also, do you need a count of the groups, which based on your sample would be `3`, or do you need a sum of all the groups, which would be `9`? – doelleri Sep 28 '15 at 19:01
  • No, definitively it is not sum. – Michael Sep 28 '15 at 21:20
  • Similar to https://stackoverflow.com/questions/9184135/how-to-countdistinct-on-multiple-columns. Late in day but may help others - a comment describing HQL way. Some how we have ended up with two different queries based on grouping sum: this `SomeClass.executeQuery("select count(distinct subClass.someThing) "+query+grpStmt,whereParams).sum()` and this: `SomeClass.executeQuery("select new map (mainClass.id as id)"+query+grpStmt,whereParams)?.size()` They both produce the same identical result but happens the distinct lookup `.sum()` is considerable faster than the latter `.size()` – V H Nov 22 '18 at 10:11

1 Answers1

2

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.

Emmanuel Rosa
  • 9,697
  • 2
  • 14
  • 20
  • I think instead of ``.size()`` you have to do ``.sum()``? – Michael Sep 28 '15 at 21:11
  • 3
    The `size()` would give you _the number of groups_ because each record represents a single grouping. `sum()` would give you the total number of devices. For example, `assert [2,3,4].size() == 3` and `assert [2,3,4].sum() == 9` – Emmanuel Rosa Sep 28 '15 at 21:21