Issue with translating SQL query into Database API (count(*))

Question

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->andConditionGroup()
    ->condition('EXERCICE', 2017, '=')
    ->condition('NUMCOLL', 89519, '=')
    ->condition('ETAT', 'R?gl?e', '=');

$con = \Database::getConnection('old_db');
$value = $con->select('PRESTATIONS', 'prestations');
$value->fields('prestations', ['LIBELLE']);
$value->condition($group);
$value->condition('PRESTATION', 'AIDES', '=');
$value->groupBy('LIBELLE');
$nb_demandes = $value->countQuery()->execute()->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

Answer 1

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->select('PRESTATIONS', 'prestations');
$query->fields('prestations', ['LIBELLE']);
$query->addExpression('COUNT(*)', 'count');
$query->condition($group);
$query->condition('PRESTATION', 'AIDES', '=');
$query->groupBy('LIBELLE');

foreach ($query->execute() as $row) {
}