SaleService.php

<?php

namespace App\Service;

use App\Entity\Sale;
use Doctrine\ORM\EntityManagerInterface;

class SaleService
{
    private EntityManagerInterface $entityManager;

    public function __construct(EntityManagerInterface $entityManager)
    {
        $this->entityManager = $entityManager;

        $emConfig = $this->entityManager->getConfiguration();
        $emConfig->addCustomDatetimeFunction('YEAR', 'DoctrineExtensions\Query\Mysql\Year');
        $emConfig->addCustomDatetimeFunction('MONTH', 'DoctrineExtensions\Query\Mysql\Month');
        $emConfig->addCustomDatetimeFunction('DAY', 'DoctrineExtensions\Query\Mysql\Day');
    }

    public function getMonthlyAveragePriceEvolution(): array
    {
        $queryBuilder = $this->entityManager->createQueryBuilder();

        $result = $queryBuilder
            ->select("MONTH(s.date) as month, YEAR(s.date) as year, AVG(s.amount / s.surface) as average_price")
            ->from(Sale::class, 's')
            ->groupBy('year, month')
            ->orderBy('year, month')
            ->getQuery()
            ->getResult();

        echo "==========================";

        return $result;
    }
}