aac2018
1 month ago

Laravel sum query using join and distinct

Posted 1 month ago by aac2018

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 sign in or create an account to participate in this conversation.