How to find the sum of specific column in cakephp

Need Help
1

Hi guys. I have a code here wherein it counts how many total payment is made. My question is, how to find the total paid amount? when im using $payment = $payment_tbl->sumOf("paid_amount");
but im getting an error Unknown method “sumOf” BadMethodCallException

$payment_tbl = TableRegistry::get("Payment");

$payment = $payment_tbl->find("all");
$payment = $payment->count();
$this->set("payment",$payment);
2

sumOf is a collection function. You can use it on the results of a query, like $payment = $payment->sumOf('paid_amount'); This runs as a loop over the results in PHP. Alternately, there’s a way to execute the sum function directly in MySQL, if that’s the only thing you’re interested in; it can range from an insignificant amount faster to a whole lot faster to the only feasible method, depending on how many rows you have in your database.

3

hi,
have a look at
https://book.cakephp.org/4/en/orm/query-builder.html
short example from there:

// Results in SELECT COUNT() count FROM …
$query = $articles->find();
$query->select([‘count’ => $query->func()->count('')]);

there are many or some few other ways to do aggregate functions, eg.:

$payment_tbl = TableRegistry::get(“Payment”);

$payment = $payment_tbl->find();
$payment->select([
‘payment_total’ => ‘sum(paid_amount)’,
‘payment_count’ => ‘cnt(*)’,
‘payment_count2’ => $payments → func() → count(‘*’),
‘payment_count2’ => $payments → func() → sum(‘paid_amount’),
]);

$this->set(“payment”,$payment);
…
and in your view then:
echo $payment->payment_total;
…

note: this is very basic and should give you an idea, also to check the good examples in the documentation …

4

i follow your answer and suggestion but its not showing the output. no error is shown.

5

can you provide some codesnippets how your select and set method looks like ? … and how you output in your template … if possible you could check with debug($payment); in the controller the content
try also :
$payments = TableRegistry::getTableLocator()->get(‘Payments’);

6

i am asking myself if your table really is named „Payment“ and not Payments … but if you refer to a wrong table, you should an error … as said a bit more code from you would help a lot

7

this is my code in controller
$payment_tbl = TableRegistry::get(“MembershipPayment”);

$payment = $payment_tbl->find(“all”);
$payment = $payment->count();
$this->set(“payment”,$payment);

then in view echo $payment;

the code is working and is showing the number of transaction made… how can i output the sum of amount payments made ?

thank you for answering

8

should work:
… you need to replace the ‘yoursumfield here’

$payment_tbl = TableRegistry:: getTableLocator()->get(“MembershipPayment”);

$payment = $payment_tbl->find();
$payment->select( ['totalpmnt' => $payment->func()->sum('yoursumfield here') ]);

$this->set('payment',$payment->totalpmnt);

then in view
echo $payment;
should contain the sum.

or, if you do
$this->set('payment', $payment);
you need to do in your view:
echo $payment->totalpmnt;
or
<?= $payment->totalpmnt ?>