I have 2 tables to query using join namely,
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
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 to
27,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();