Display total sale sql query


#1

Hi, Im still new learning cakephp. I having a hard time trying to display custom query for total sale of all my user group by year and month.

This is my code.

in userController.
> public function sale()

{
$this->loadModel(‘Purchases’);
$result=$this->Purchases->query('SELECT
year(date) as SalesYear,
month(date) as SalesMonth,
sum(totalPrice) AS TotalSales
FROM Purchases
GROUP BY year(date), month(date)
ORDER BY year(date), month(date)
');
$this->set(‘result’,$result);
}

in my sale.ctp

<?php foreach ($result as $result): ?>
 <p><?php echo $result; ?>   </p>

<?php endforeach; ?>    

but the ouput that i get is:

{ “id”: 1, “date”: “2018-11-07T00:00:00+00:00”, “quantity”: 1, “item_id”: 1, “user_id”: 1, “totalPrice”: 60 }
{ “id”: 2, “date”: “2018-11-08T00:00:00+00:00”, “quantity”: 1, “item_id”: 2, “user_id”: 3, “totalPrice”: 80 }

I tried read the documentation but got confuse with find().

I hope that you guys can help me :sweat_smile: