0

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

  • 1
    You need to use addExpression to add an expression like count(*). Why would you rather avoid it? – Clive Sep 15 '17 at 16:42
  • Well, it's more about what I call clean code, but it doesn't matter. Can I ask you to show me how it should be done with addExpression(), please? – Jaeger Sep 15 '17 at 16:50
2

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) {
}
  • It was easier than I thought, but I couldn't figure how to put it together, thanks a lot! – Jaeger Sep 15 '17 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.