How can I divide two HQL Count Results?

Question

I want to compute the percentage of people who are not active divided by the percentage of people being active.

I will do something like the following HQL query but it did not work:

(select count(x) from People as x where x.active=false) / 
(select count(x) from People as x where x.active=true)

How can I do that?

What doesn't work? If you're getting an NPE it could be from [this bug](https://hibernate.atlassian.net/browse/HHH-2917) — doelleri, Aug 12 '13 at 20:36

score 1 · Answer 1 · answered Aug 12 '13 at 20:52

1

You can use a group by clause to express this. Here's how you could do it in Grails:

def countByAction = People.
    executeQuery("select active, count(*) from People group by active")*.
    toList().
    collectEntries{it}
println (countByAction[true])
println (countByAction[false])

answered Aug 12 '13 at 20:52

ataylor

64,891
24
161
189

Hi ataylor, I don't understand this... does it do what the user requested? can you add a description of how this works? – Samuel Parsonage Jan 08 '16 at 04:49
It uses a HQL group by clause to split the query results into groups. In particular, it groups by the `active` column. Since `active` is a boolean field, it will partition the results into two groups for true and false. The `count(*)` selects the count of records in each group. The results of the query would be something like `[[true, 10], [false, 20]]`. This might be helpful to understand grouping in HQL/SQL: http://stackoverflow.com/questions/7434657/ – ataylor Jan 08 '16 at 05:04

score 0 · Answer 2 · answered Aug 13 '13 at 07:48

Since you are using HQL expression, I'm assuming you are using Hibernate. In this case I would recommend you using @Formula annotation. You may find additional information here:

score 0 · Answer 3 · answered Aug 13 '13 at 15:04

Something like this should work

String hql = "select count(x)/(select count(x) from People as x where x.active=true) 
                 from People as x where x.active=false";

Long result = (Long) em.createQuery(hql).getSingleResult();

NOTE: above is untested

score 0 · Answer 4 · answered Aug 13 '13 at 16:24

0

I use countBy* Dynamic method that uses the properties of the domain class to query for the count of the number of matching records. see http://grails.org/doc/latest/ref/Domain%20Classes/countBy.html

answered Aug 13 '13 at 16:24

Olencha

418
2
11

How can I divide two HQL Count Results?

4 Answers4