Merge a SQL query loop into a single SQL query

Question

I built a loop in PHP that makes 500 SQL queries, but I would like to combine 500 SQL queries into one and get the same income (companies and the number of users in each company)

PHP code example

$companies =  array();
foreach ($fortune500Service->listAll() as $c ){
  $count = $entityManager
  ->createQueryBuilder()
  ->select("count(u)")
  ->from("AppBundle\Entity\User","u")
  ->where("u.email LIKE :d")
  ->setParameter("d", "%@" . $c["Domain"])
  ->getQuery()->getSingleScalarResult();

  if ($count == 0) {
    continue;
  }

  $companies[] = array(
      "Domain" => $c["Domain"],
      "Company" => "{$c["Company"]} ({$count})",
  );
}
return $companies;

An example of two SQL queries that I want to combine

Request 1

SELECT 
  count(u0_.id) 
FROM 
  user u0_ 
WHERE 
  u0_.email LIKE '[e-mail]' 

Request 2

SELECT 
  count(u0_.id)
FROM 
  user u0_ 
WHERE 
  u0_.email LIKE '[e-mail]' 

I prefer the solution using createQueryBuilder http://symfony.com/doc/current/book/doctrine.html#querying-for-objects-using-doctrine-s-query-builder , but I am also happy with the original SQL query.

Answer 1

Use conditional aggregation:

SELECT COUNT(CASE WHEN u0_.email LIKE '[e-mail]' THEN 1 END) as First_cnt,
        COUNT(CASE WHEN u0_.email LIKE '[e-mail]' THEN 1 END) as First_cnt
FROM user u0_