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
addExpression
to add an expression likecount(*)
. Why would you rather avoid it?addExpression()
, please?