19

I have the following classes: 

class User {
  hasMany = [ratings: Rating] 
} 

class Item {
 hasMany = [ratings: Rating]
}

class Rating {
 belongsTo = [user: User, item: Item]
}

I want to count the distinct users that rated on an item.

The following does not work: 

select count(distinct(r.user)) from Rating as r
        where r.item=:item
        group by r.user

How do I have to modify the HQL query to make it work?

dmahapatro
  • 49,365
  • 7
  • 88
  • 117
Michael
  • 32,527
  • 49
  • 210
  • 370

3 Answers3

46

Your query should work as expected with a minor modification to the way you use distinct:

select count(distinct r.user) from Rating as r 
where r.item = :item group by r.user

An other, but more lengthy way, of doing this query is by using User and join: 

select count(distinct u) from User as u 
inner join u.ratings as r where r.item = :item
group by r.user
john
  • 613
  • 1
  • 7
  • 25
dmahapatro
  • 49,365
  • 7
  • 88
  • 117
  • 1
    It's really strange that the extra parens inside of the count on the distinct cause it to fail with a syntax error. This is the correct fix. Certainly saved me tons of time. – Jason D Dec 15 '14 at 16:42
0

This is how to do in Hibernate Criteria 

Criteria crit = session.createCriteria(Rating.class)
.add(Restrictions.like("item", item)
.addOrder(Order.asc("user"))
.setProjection(
       Projections.distinct(Projections.projectionList()
                        .add(Projections.property("user"), "user")))
.setResultTransformer(Transformers.aliasToBean(Rating.class));
aksappy
  • 3,400
  • 3
  • 23
  • 49
  • How can I do that with HQL? – Michael Sep 13 '13 at 17:17
  • 1
    select distinct r.user from Rating r where r.item=:item group by r.user; Then do a size()? – aksappy Sep 13 '13 at 17:24
  • is there a way to do it in one query? – Michael Sep 13 '13 at 17:52
  • It's inneficient doing it that way, since you will be listing *every* record just to see how many of them are present. For a better example using criteria, please see http://stackoverflow.com/questions/22164717/hibernate-criteria-for-finding-count-for-distinct-value – Haroldo_OK Mar 24 '15 at 17:02
  • Note that the criteria API has a Projections.countDistinct(). If you use that it will give the correct behavior with a lot less code. I think you can use crit.setProjection(Projections.countDistinct("user")); – Jon Apr 16 '18 at 17:18
0

Use distinct as keyword not function, inside count function like-

count(distinct column) : Works

count(distinct(column)): Doesn't work
iamfnizami
  • 163
  • 1
  • 8