Distinct Count across multiple columns

crancran · **Joined:** Fri Nov 13, 2009 4:05 pm **Posts:** 30

I have been searching for the last few days for a solution to a COUNT DISTINCT problem but I have yet to find a resolution. In a reporting application, I need to perform an aggregation SQL statement as follows:

Code:

SELECT tbl.salesRep as X,
            COUNT(DISTINCT tbl.item, tbl.vendor) as Y
   FROM mySalestable tbl
 WHERE facilityId in (:facilityIds)
GROUP BY tbl.salesRep

When using HQL I get the following error:
org.hibernate.hql.ast.QuerySyntaxException: expecting CLOSE, found ',' near line 1, column 61

Is this not possible? Can someone show me how to do this with either HQL or Criteria API?

clausweng · **Joined:** Mon Jul 29, 2013 7:14 am **Posts:** 1

Did you ever find a solution on this issue?

Ajit Singh · **Joined:** Wed Feb 06, 2013 2:43 am **Posts:** 46

Here is a solutions for simple query using criteria :

Integer itemCount = (Integer) session.createCriteria(Student.class).
setProjection(Projections.countDistinct("item"))
.uniqueResult();

In this way you can get the distinct results , but if you want for a complex query, this can not be done using HQL