How use sql aggregat SUM in Symfony

Question

From the table Invoice ( id, invoice_id, produit, price).

I'm trying to do something like this in Symfony with doctrine:

SELECT SUM(price) AS price_total
FROM Invoice
WHERE invoice_id = 1

Here is my request in InvoiceRepository.php:

 $qb = $this->_em->createQueryBuilder("i");
        $qb->select('SUM(i.price)')
            ->from('AppBundle:Invoice','i')
            ->where('i.invoice_id = :invoice')
            ->setParameter('invoice',$invoice);
        return $qb->getQuery()->getSingleScalarResult();

But this request always return me the first value and not the sum.

Someone know what's wrong ?

Answer 1

To use aggregate functions like SUM, AVG, COUNT etc, you need to specify GROUP BY condition. Like this:

$qb = $this->_em->createQueryBuilder("i");
    $qb->select('SUM(i.price)')
        ->from('AppBundle:Invoice','i')
        ->where('i.invoice_id = :invoice')
        ->groupBy('i.invoice_id') //<- here
        ->setParameter('invoice',$invoice);
    return $qb->getQuery()->getSingleScalarResult();