Pagination fails with group by clause

[Daemach]

In semi-hairy queries like the one below, the group by clause causes pagination to fail because the "count(*) as aggregate" to figure out page lengths returns more than one row. I don't know what to do about this currently other than running 3 queries, which is non-performant - first query to select into a temp table, second to do the pagination with qb and third to drop the temp table. seems inelegant.

One possible alternate method might be to, if you have a group by clause, pull the last element from the group by list, select just that field and get a recordcount. Should be nearly as fast as a count(*) and won't break things.

qb.newQuery()
  .from("mars_out_hold_sum hs")
  .join("mars_in_security s", 'hs.security_id', 's.security_id')
  .where('hs.as_of_date', date)
  .andwhere('change_shares','<>',0)
  .andWhere('user_id', event.getValue('user_id'))
  .andWhere('batch_name', event.getValue('batch_name'))
  .andWhere('account_id','<>',"cash")
  .when(!isNull(rc.project_name),(q)=>{
    q.andWhere('project_name', event.getValue('project_name',0))
  })
  .when(filter.len(),(q)=>{
    q.andWhere((q2)=>{
      q2.whereLike('account_name','%#filter#%')
        .orWhereLike('hs.account_id','%#filter#%')
        .orWhereLike('ticker','%#filter#%')
        .orWhereLike('s.security_name','%#filter#%')
    })
  })
  .selectRaw("CASE WHEN min(change_shares) < 0 THEN 'SELL' ELSE 'BUY' END AS buy_sell, batch_name, project_name, account_id, account_name, ticker, s.security_name, price, sum(change_shares) AS change_shares")
  .groupBy( "batch_name, project_name, account_id, account_name, ticker, s.security_name, price" )
  .distinct()
  .orderBy(event.getValue('sortBy','type'), (event.getValue('descending', true)) ? 'desc': 'asc')
  .paginate( page=event.getValue('page',1), maxRows= event.getValue('rowsPerPage',15), options={datasource:'mars'} )