# createCountQuery with Hibernate 6.6.2

**URL:** <https://discourse.hibernate.org/t/createcountquery-with-hibernate-6-6-2/10616>\
**Category:** Hibernate ORM\
**Created:** [November 14, 2024, 4:36pm UTC](https://discourse.hibernate.org/t/createcountquery-with-hibernate-6-6-2/10616 "2024-11-14T16:36:14Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Pat](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/pat/32/2565_2.png) [@Pat](https://discourse.hibernate.org/u/Pat)\
**Post date:** [November 14, 2024, 4:36pm UTC](https://discourse.hibernate.org/t/createcountquery-with-hibernate-6-6-2/10616/1 "2024-11-14T16:36:14Z")

</div>

When creating a countQuery the countQuery contains the orderby criteria from the source query, and if this is also a distinct query, the countQuery fails:

```auto
final CriteriaQuery<Organization> query = criteriaQuery
                .where(criteriaBuilder.and(predicates.toArray(Predicate[]::new)));
        query.distinct(true);

        final Sort sortBy = page.getSortOr(Sort.by(Sort.Direction.ASC, Fields.someId));

        query.orderBy(sortBy.stream().map(order -> order.isAscending() //
                        ? criteriaBuilder.asc(root.get(order.getProperty())) //
                        : criteriaBuilder.desc(root.get(order.getProperty()))).toList());

         **((SqmSelectStatement<Organization>) query).createCountQuery();**

```

This ends in sql like this:

```auto
    select
        count(*) 
    from
        (select
            distinct o1_0.id 
        from
            orgs o1_0 
        where
            o1_0.groups && cast(array['COMMON,'SPECIAL'] as varchar array)::text[] 
            and lower(o1_0.id)=? 
            order by
            o1_0.org_id) derived1_0(c0_id)

```

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

How can I avoid this?

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [November 14, 2024, 4:47pm UTC](https://discourse.hibernate.org/t/createcountquery-with-hibernate-6-6-2/10616/2 "2024-11-14T16:47:11Z")

</div>

What version of Hibernate are you using? This might be a bug, please try to create a reproducer using our [test case template](https://github.com/hibernate/hibernate-test-case-templates/blob/master/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java) and if you are able to reproduce the issue, create a new ticket in our [issue tracker](https://hibernate.atlassian.net/browse/HHH) and attach that reproducer.

---

<div class="post-metadata">

**Author:** ![Pat](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/pat/32/2565_2.png) [@Pat](https://discourse.hibernate.org/u/Pat)\
**Post date:** [November 15, 2024, 9:18am UTC](https://discourse.hibernate.org/t/createcountquery-with-hibernate-6-6-2/10616/3 "2024-11-15T09:18:06Z")

</div>

Hi, thanks for the quick reply!  
I used 6.6.2.  
Issue created:  
[https://hibernate.atlassian.net/browse/HHH-18850](https://hibernate.atlassian.net/browse/HHH-18850)

Reproducer is attached.