[FIXED] Calculate total price of database records in cakephp 3.x

Issue

I'd like to calculate the total price of all the records in my Invoices table with Cakephp. For example these are some of the columns i'm using in my Mysql database with value:

Invoice number (INT) = 000123

Invoice price (decimal(19,2)) = 12.50.

I've tried to use Array_sum but when i did that it said the field must be an Integer. Is there an easy way to get the total price directly in the Controller and send it to the view?

Solution

You can use sum() function with func() method like this:

$query = $this->Invoices->find();
$query->SELECT(['price'=>$q->func()->sum('price')]);

For results use:

$result = $query->toArray();
// or $query->all();
// now you can set result for view

And supposing that you have price field in Invoices table and you want to sum all prices within that. You can add your conditions as you need.

Answered By - Manohar Khadka