SQL Subquery IN SELECT [Symfony3]

Question

I Have Table Actions with the following fields :

idAction
Cloture
Date

I'd Like to loop through and display a list of all idAction In my DB + idAction with Cloture = 0 Group By the same Date (both of them).

I tried below method. But it doesn't work. Can anyone help me?

$query = $this->getEntityManager()->createQuery(

'SELECT COUNT(a.idAction) AS nmbreAction , week(a.dateOuverture) AS week,( SELECT COUNT(c.idAction) , week(c.dateOuverture) FROM ActionActionBundle:Action c 
WHERE c.cloture = 0 ) AS nmbreRetard FROM ActionActionBundle:Action a 
GROUP BY week');

sagi · Accepted Answer · 2017-09-25T16:03:31.593

Mmm, you question lacks a lot of information. Is this what you need?

SELECT COUNT(a.idAction) AS nmbreAction ,
       week(a.dateOuverture) AS week,
       (SELECT COUNT(c.idAction) 
        FROM ActionActionBundle:Action c
        WHERE c.cloture = 0 
          and week(c.dateOuverture) = week(a.dateOuverture)) AS nmbreRetard 
FROM ActionActionBundle:Action a
GROUP BY week(a.dateOuverture)

You can't select more than 1 column in a sub query\correlated query in the select list, which was probably showed to you in the error message.

EDIT: Better of do that:

SELECT COUNT(a.idAction) AS nmbreAction ,
       week(a.dateOuverture) AS week,
       COUNT(CASE WHEN a.cloture = 0 THEN 1 END) as nmbreRetard
FROM ActionActionBundle:Action a
GROUP BY week(a.dateOuverture)

@M.Mery No problem. Look at the edit, a better and more efficient way to do this. No need for this extra select . — sagi, Sep 25 '17 at 16:03

SQL Subquery IN SELECT [Symfony3]

1 Answers1