Try ->groupBy('loans.loan_type', 'loans.id');
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();
Please or to participate in this conversation.