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

aac2018's avatar

Laravel sum query using join and distinct

I have 2 tables to query using join namely, loans and amortizations table. Loans table has many amortizations, so loans.id is reference as amortizations.loan_id in amortizations table. Loan id:3 exist twice in amortizations table, each with a value=10,800. Loan id:5 also exist twice in amortizations table, each with a value=11,100 and the same goes with Loan id:11 that also exist twice in amortizations table each with a value=5400. When you add this up, the total is 54,600. But I wanted the ids to be distinct so that the values won't add twice. So the total would be equal to27,300. My existing query below returns a 54,600 value instead of 27,300 even using distinct() in laravel. How would I restructure my code. Please help. Thanks a lot.

                    $oneToThirtyDue = Amortization::distinct('amortizations.loan_id')
                      ->join('loans', 'loans.id', '=', 'amortizations.loan_id')
                      ->select('loans.loan_type',\DB::raw('SUM(loans.loan_balance) as total_val'))
                      ->where('amortizations.payment_status',0)
                      ->where('amortizations.schedule', '<', date('Y-m-d'))
                      ->where('amortizations.past_due', '<=', 30)
                      ->groupBy('loans.loan_type')
                      ->orderBy('loans.loan_type')
                      ->get();
0 likes
4 replies
Sti3bas's avatar

Try ->groupBy('loans.loan_type', 'loans.id');

jlrdw's avatar

These types of queries can take some trial and error, so maybe just play around with it a little bit.

Make sure you have the order of the statements correct.

You probably don't need the distinct with the loans.id added in group by.

Please or to participate in this conversation.