Doctrine Query Builder Select Count with Case

Question

I have the following sql-statement that I want to transform into doctrine query builder. The goal is to count how many ratings exist with rating value 1 and with rating value 2.

SELECT 
COUNT(CASE WHEN rating.rating = 1 THEN rating.rating END) as rat1, 
COUNT(CASE WHEN rating.rating = 2 THEN rating.rating END) as rat2 
FROM rating 

This sql statement is working fine - but when I try to transform it into a Doctrine statement, it does not anymore. When nothing should get counted (because no ratings for this value exist), it returns me a "1" instead of a 0. How can I tell doctrine to simply return a zero when there is nothing to count? I tried it by removing the "ELSE 0" , but then I get an error that this part is required..

return $qb
        ->addSelect('COUNT(CASE WHEN r.rating = 1 THEN r.rating ELSE 0 END) as rat_1')
        ->addSelect('COUNT(CASE WHEN r.rating = 2 THEN r.rating ELSE 0 END) as rat_2')
        ->getQuery()
        ->getResult();

Regards,

"sum" is not required - example: votings 2,2,2,2,2 should return 5 , because the rating with value 2 got voted 5 times.

Answer 1

To count distinct id's in one column depending on the value in another column the answer from Fuzzy Tree does not work in this case.

To count only the distinct values you should give null as a parameter and set it to null like:

->addSelect('COUNT(DISTINCT(CASE WHEN r.rating = 1 THEN rating.rating ELSE :nada END)) as rat_1')
->setParameter(':nada', null)

Answer 2

As vkp mentioned, you can use SUM but instead of summing the rating, sum either a 1 or a 0 to simulate a COUNT

SUM(CASE WHEN r.rating = 1 THEN 1 ELSE 0 END)