Doctrine Combining count, grouped select

Question

This is my table

ID    | Name  | enabled1 |  count1  | enabled2 | count2
1     | John  |     1    |    3     |    0     |   4
2     | John  |     1    |    2     |    1     |   2
3     | Mike  |     0    |    7     |    0     |   3
4     | Simon |     1    |    3     |    1     |   2
5     | John  |     0    |    2     |    1     |   7
6     | Mike  |     0    |    2     |    0     |   1
7     | Marco |     1    |    6     |    1     |   6
8     | John  |     1    |    9     |    0     |   2
9     | Marco |     0    |    4     |    0     |   1

And this is my doctrine function:

public function countCombined() : array
{
   $qb = $this->createQueryBuilder('u');
   $qb->select('u.name, COUNT(u.id), SUM(u.count1) as count1, SUM(u.count2) as count2');

   $qb->where(
       $qb->expr()->andX(
          $qb->expr()->eq('u.enabled1', ':enabled'),
              $qb->expr()->eq('u.enabled2', ':enabled'), // this should not be here
       )
   );

   $qb->setParameter('enabled', '1');

   $qb->groupBy('u.name');

   return $qb->getQuery()->getResult();
}

the function should give me an array of grouped names with sum of count1 if enabled1 is active, the same is for count2 and enabled2. But my idea is if it is possiable to make it in one function or do i need to make another one?

it should give me something like this back:

Name  | count1  | count2 | NamesCounted
John  |   14    |    8   |     4
Mike  |   0     |    0   |     2
Simon |   3     |    2   |     1
Marco |   6     |    6   |     2

counts all of the grouped named, and counts1 when enabled1 is active, counts2 if enabled2 is active.

Thank you.

Answer 1

Here is what you are looking for :

select 
  Name,
  sum(case when enabled1=1 then count1 end) as count1,
  sum(case when enabled2=1 then count2 end) as count2,
  count(id) as NamesCounted
from myTable
group by Name

DEMO HERE