Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

JavedBaloch's avatar

How to sum amount / balance in collection.

Hi,

Creating app here three tables are associated with transactions, 1) cashbook 2) customers, Now in my cashbook table I have two columns credit & debit, How do I sum all credit or debits of specific user with specific date.

Thanks,

Here is my query which returns collections, So how can sum all credit and debit column in the all collections?

$transactions = Transaction::where('user_type', '=', 'customer')
            ->where('user_type_id', '=', $request->customer_id)
            ->whereBetween('date', [$from, $to])
            ->get();
0 likes
9 replies
Sinnbeck's avatar

You can the sum collection method.

$transactions = Transaction::where('user_type', '=', 'customer')
            ->where('user_type_id', '=', $request->customer_id)
            ->whereBetween('date', [$from, $to])
            ->get();

$credit = $transactions->sum('credit');
$debit = $transactions->sum('debit');
ftiersch's avatar

Does your Transaction model have a credit / debit column? Is that the model that accesses cashbook?

JavedBaloch's avatar

No, basically I am using relation,

Transaction Model.

public function cashbook() {
        return $this->hasOne(Cashbook::class);
}

Credit & Debit Columns are in cashbook model.

Cashbook Model.

 public function transaction() {
        return $this->belongsTo(Transaction::class);
    }
ftiersch's avatar

Ah okay. Then you could do:

->get()
->sum(function ($transaction) {
    return !empty($transaction->cashbook->credit) ? $transaction->cashbook->credit : $transaction->cashbook->debit;
});
JavedBaloch's avatar

I think this isn't working properly how do i define my problem let me try,

I need something like that,

$credit = $transactions[Every Instance]->cashbook->credit->sum()

$debit = $transactions[Every Instance]->cashbook->debit->sum()

Basically a cashbook can have many transactions now I want to sum all the credits and debits inside cashbook table

asadalarma's avatar

There are two ways to achieve this

1 ) one way to iterate over transaction and calculate the running total Formula $closingBalance = $userPaymentHistory->debit - $userPaymentHistory->credit; depends on your legder and company's accounting

                        @php
                        $runningTotal = 0;
                        @endphp
                        @foreach($userPaymentHistories as $key => $userPaymentHistory)
                            @php
                            $closingBalance = $userPaymentHistory->credit - $userPaymentHistory->debit;
                            $runningTotal += $closingBalance;
                            @endphp

running total contains all the balance calculation

2 ) second way to calculate over database select credit , debit , FORMAT (SUM(credit) OVER ( ORDER BY id ) - SUM(debit) OVER ( ORDER BY id ),2) AS balance from relevant_table

Please or to participate in this conversation.