setCountQuery example

$count_query = clone $query;
    $count_query->addExpression('COUNT([u].[uid])');

    $query = $query
      ->extend(PagerSelectExtender::class)
      ->extend(TableSortExtender::class);
    $query
      ->fields('u', ['uid'])
      ->limit(50)
      ->orderByHeader($header)
      ->setCountQuery($count_query);
    $uids = $query
      ->execute()
      ->fetchCol();

    $options = [];

    foreach (User::loadMultiple($uids) as $account) {
      $options[$account->id()] = [
        'title' => ['data' => ['#title' => $account->getAccountName()]],
        'username' => $account->getAccountName(),
        'status' => $account->isActive() ? t('active') : t('blocked'),
      ];

$count_query->addExpression('COUNT(*)');
      $count_query
        ->condition('c.entity_id', $entity->id())
        ->condition('c.entity_type', $entity->getEntityTypeId())
        ->condition('c.field_name', $field_name)
        ->condition('c.default_langcode', 1)
        ->addTag('entity_access')
        ->addTag('comment_filter')
        ->addMetaData('base_table', 'comment')
        ->addMetaData('entity', $entity)
        ->addMetaData('field_name', $field_name);
      $query->setCountQuery($count_query);
    }

    if (!$this->currentUser->hasPermission('administer comments')) {
      $query->condition('c.status', CommentInterface::PUBLISHED);
      if ($comments_per_page) {
        $count_query->condition('c.status', CommentInterface::PUBLISHED);
      }
    }
    if ($mode == CommentManagerInterface::COMMENT_MODE_FLAT) {
      $query->orderBy('c.cid', 'ASC');
    }
    

$query = $this->database->select('watchdog', 'w')
      ->extend(PagerSelectExtender::class)
      ->extend(TableSortExtender::class);
    $query->addExpression('COUNT([wid])', 'count');
    $query = $query
      ->fields('w', ['message', 'variables'])
      ->condition('w.type', $type)
      ->groupBy('message')
      ->groupBy('variables')
      ->limit(30)
      ->orderByHeader($header);
    $query->setCountQuery($count_query);
    $result = $query->execute();

    $rows = [];
    foreach ($result as $dblog) {
      if ($message = $this->formatMessage($dblog)) {
        $rows[] = [$dblog->count, $message];
      }
    }

    $build['dblog_top_table'] = [
      '#type' => 'table',
      

->addMetaData('base_table', 'forum_index')
      ->orderBy('f.sticky', 'DESC')
      ->orderByHeader($header)
      ->limit($forum_per_page);

    $count_query = $this->connection->select('forum_index', 'f');
    $count_query->condition('f.tid', $tid);
    $count_query->addExpression('COUNT(*)');
    $count_query->addTag('node_access');
    $count_query->addMetaData('base_table', 'forum_index');

    $query->setCountQuery($count_query);
    $result = $query->execute();
    $nids = [];
    foreach ($result as $record) {
      $nids[] = $record->nid;
    }
    if ($nids) {
      $nodes = $this->entityTypeManager->getStorage('node')->loadMultiple($nids);

      $query = $this->connection->select('node_field_data', 'n')
        ->extend(TableSortExtender::class);
      $query->fields('n', ['nid']);