Issue with translating SQL query into Database API (count(*)) I'm currently building some SQL queries for a view that will display some statistics, and I'm having trouble with adding COUNT(*) in a couple SQL queries:

Original SQL query

SELECT LIBELLE, COUNT(*)
FROM PRESTATIONS
WHERE EXERCICE = 2017 
AND NUMCOLL = 89519
AND ETAT = 'R?gl?e'
AND PRESTATION = 'AIDES'
group by LIBELLE;


The result is the following:

+----------+----------+
| LIBELLE  | COUNT(*) |
+----------+----------+
| RESULT 1 | 1        |
+----------+----------+
| RESULT 2 | 3        |
+----------+----------+


This is how I set my code up after reading a bit of documentation:

$group = $value-&gt;andConditionGroup()
    -&gt;condition('EXERCICE', 2017, '=')
    -&gt;condition('NUMCOLL', 89519, '=')
    -&gt;condition('ETAT', 'R?gl?e', '=');

$con = \Database::getConnection('old_db');
$value = $con-&gt;select('PRESTATIONS', 'prestations');
$value-&gt;fields('prestations', ['LIBELLE']);
$value-&gt;condition($group);
$value-&gt;condition('PRESTATION', 'AIDES', '=');
$value-&gt;groupBy('LIBELLE');
$nb_demandes = $value-&gt;countQuery()-&gt;execute()-&gt;fetchAll();


However, when executing this code, I have the following error:


  Syntax error or access violation: 1055
  'old_db.prestations.PRESTATION' isn't in GROUP BY: SELECT COUNT(*)
  AS expression FROM (SELECT prestations.PRESTATION AS PRESTATION,
  prestations.LIBELLE AS LIBELLE, 1 AS expression FROM {PRESTATIONS}
  prestations WHERE ( (EXERCICE = :db_condition_placeholder_0) AND
  (NUMCOLL = :db_condition_placeholder_1) AND (ETAT =
  :db_condition_placeholder_2) )AND( (EXERCICE =
  :db_condition_placeholder_3) AND (NUMCOLL =
  :db_condition_placeholder_4) AND (ETAT = :db_condition_placeholder_5)
  )AND (PRESTATION = :db_condition_placeholder_6) GROUP BY LIBELLE)
  subquery; Array ( [:db_condition_placeholder_0] => 2017
  [:db_condition_placeholder_1] => 89519 [:db_condition_placeholder_2]
  => R?gl?e [:db_condition_placeholder_3] => 2017 [:db_condition_placeholder_4] => 89519 [:db_condition_placeholder_5]
  => R?gl?e [:db_condition_placeholder_6] => AIDES )


I do believe I didn't set up my COUNT(*) correctly but I can't find another way to do so.

How can I solve this?

Thank you in advance
As @Clive said, just add an expression to your first query. countQuery() is only about getting a single count for a query and in many cases it is very inefficient.

$con = \Database::getConnection('old_db');
$query = $con-&gt;select('PRESTATIONS', 'prestations');
$query-&gt;fields('prestations', ['LIBELLE']);
$query-&gt;addExpression('COUNT(*)', 'count');
$query-&gt;condition($group);
$query-&gt;condition('PRESTATION', 'AIDES', '=');
$query-&gt;groupBy('LIBELLE');

foreach ($query-&gt;execute() as $row) {
}