title: Hibernate Criteria Row Count of Group By Query
body: <p>I am trying to fetch the results of a group by query with a hibernate criteria. As you can see in the code below, I am adding lots of projections into a ProjectionList. I have a pagination in front-end so if the client wants to fetch 20 results, I'm setting <code>firstResult</code> to <code>0</code> and <code>maxResults</code> to <code>20</code>. </p>
  
  <p>I need send total row count of this query, too. However, if I use <code>setProjection(Projections.rowCount)</code> it will overwrite my <code>groupby</code>, <code>sum</code>, and <code>max</code> projections.</p>
  
  <p>How can I write something like <code>select count(*) from ( myCriteria )</code>?</p>
  
  <pre><code>@Override
  public SearchResult&lt;ServiceIncomeSO&gt; findServiceIncomesBySearch(IncomeQuery query, Pager pager) {
      Criteria criteria = createCriteria(ServiceIncome.class, "sin");
      criteria.createAlias("sin.partner", "ptr", JoinType.LEFT_OUTER_JOIN);
  
      if (StringUtils.isNotBlank(query.getPartnerKey())) {
          criteria.add(eq("ptr.key", query.getPartnerKey()));
      }
  
      if (StringUtils.isNotBlank(query.getPartnerTxt())) {
          criteria.add(disjunction() //
              .add(ilike("ptr.key", query.getPartnerTxt(), ANYWHERE)) //
              .add(ilike("ptr.name", query.getPartnerTxt(), ANYWHERE)) //
              .add(ilike("ptr.title", query.getPartnerTxt(), ANYWHERE))); //
      }
  
      if (CollectionUtils.isNotEmpty(query.getPartnerTypes())) {
          criteria.createAlias("ptr.partnerTypes", "partnerType", JoinType.LEFT_OUTER_JOIN);
          criteria.add(in("partnerType." + COLLECTION_ELEMENTS, query.getPartnerTypes()));
      }
  
      if (CollectionUtils.isNotEmpty(query.getCategories())) {
          criteria.createAlias("ptr.categories", "cat", JoinType.LEFT_OUTER_JOIN);
          criteria.add(in("cat.key", query.getCategories()));
      }
  
      if (query.getPeriodFromMonth() != null &amp;&amp; query.getPeriodFromYear() != null) {
          addPeriodFrom(criteria, "sin.year", "sin.month", query.getPeriodFromYear(), query.getPeriodFromMonth());
      }
  
      if (query.getPeriodToMonth() != null &amp;&amp; query.getPeriodToYear() != null) {
          addPeriodTo(criteria, "sin.year", "sin.month", query.getPeriodToYear(), query.getPeriodToMonth());
      }
  
      String[] fields;
      ProjectionList projection;
      if (query.getBreakout() == QueryBreakout.PARTNER) {
          projection = Projections.projectionList() //
              .add(Projections.groupProperty("sin.partner.key")) //
              .add(Projections.groupProperty("sin.year")) //
              .add(Projections.groupProperty("sin.month")) //
              .add(Projections.sum("sin.subscriberCount")) //
              .add(Projections.sum("sin.income"));//
          fields = new String[] { "partnerKey", "year", "month", "subscriberCount", "income" };
      } else {
          projection = Projections.projectionList() //
              .add(Projections.groupProperty("sin.partner.key")) //
              .add(Projections.groupProperty("sin.service.id")) //
              .add(Projections.groupProperty("sin.year")) //
              .add(Projections.groupProperty("sin.month")) //
              .add(Projections.max("sin.shortNumber")) //
              .add(Projections.sum("sin.subscriberCount")) //
              .add(Projections.sum("sin.income")) //
          ; //
          fields = new String[] { "partnerKey", "serviceId", "year", "month", "shortNumber", "subscriberCount", "income" };
      }
  
      criteria.setProjection(projection);
      criteria.addOrder(desc("sin.year")).addOrder(desc("sin.month"));
  
      criteria.setFirstResult(pager.getOffset());
      criteria.setMaxResults(pager.getMax());
  
      List&lt;ServiceIncomeSO&gt; list = ((List&lt;Object[]&gt;) criteria.list()).stream() //
          .map(values -&gt; JavaLangUtils.setProperties(new ServiceIncomeSO(), fields, values)) //
          .collect(Collectors.toList()); //
  
      // TODO How to get count without retrieving all the data?
      int totalResults = 0;
  
      return new SearchResult&lt;&gt;(list, totalResults, pager.getMax(), pager.getOffset());
  }
  
  private void addPeriodFrom(Criteria criteria, String yearColumn, String periodColumn, int yearFrom, int periodFrom) {
      criteria.add(disjunction() // OR
          .add(and(eq(yearColumn, yearFrom), ge(periodColumn, periodFrom))) // year == yearFrom &amp;&amp; period &gt;= periodFrom
          .add(gt(yearColumn, yearFrom))); // year &gt; yearFrom
  }
  
  private void addPeriodTo(Criteria criteria, String yearColumn, String periodColumn, int yearFrom, int periodFrom) {
      criteria.add(disjunction() // OR
          .add(and(eq(yearColumn, yearFrom), le(periodColumn, periodFrom))) // year == yearFrom &amp;&amp; period &lt;= periodFrom
          .add(lt(yearColumn, yearFrom))); // year &lt; yearFrom
  }
  </code></pre>
  
  <p>I can write the same query in plain SQL but cannot do it in Hibernate Criteria API.</p>
  
  <p>Select Query:</p>
  
  <pre><code>SELECT 
      PARTNER_KEY, 
      SERVICE_ID, 
      INCOME_YEAR, 
      INCOME_MONTH, 
      SUM(SUBSCRIBER_COUNT), 
      SUM(INCOME) 
  FROM 
      PP_SERVICE_INCOME 
  WHERE 
      PARTNER_KEY = 'PART32143' 
  GROUP BY 
      PARTNER_KEY, 
      SERVICE_ID, 
      INCOME_YEAR, 
      INCOME_MONTH;
  </code></pre>
  
  <p>Select Result:</p>
  
  <pre><code>PARTNER_KEY SERVICE_ID INCOME_YEAR INCOME_MONTH SUM(SUBSCRIBER_COUNT) SUM(INCOME) 
  ----------- ---------- ----------- ------------ --------------------- ----------- 
  PART32143   1          2016        1            1234                  175000      
  PART32143   1          2017        1            1234                  175390      
  PART32143   1          2016        6            1234                  151100      
  PART32143   1          2017        0            1234                  157800      
  PART32143   1          2016        7            1234                  175220      
  PART32143   1          2016        2            1234                  143000      
  PART32143   1          2016        0            1234                  150000      
  PART32143   1          2017        2            1234                  143012      
  PART32143   1          2016        8            1234                  143330      
  </code></pre>
  
  <p>Count Query:</p>
  
  <pre><code>SELECT 
      COUNT(*) 
  FROM (
      SELECT 
          PARTNER_KEY, 
          SERVICE_ID, 
          INCOME_YEAR, 
          INCOME_MONTH, 
          SUM(SUBSCRIBER_COUNT), 
          SUM(INCOME) 
      FROM 
          PP_SERVICE_INCOME 
      WHERE 
          PARTNER_KEY = 'PART32143' 
      GROUP BY 
          PARTNER_KEY, 
          SERVICE_ID, 
          INCOME_YEAR, 
          INCOME_MONTH
      );
  </code></pre>
  
  <p>Count Result: 9</p>