Eloquent Query with join returning double the sum of the actual values in Laravel

Random guess. This should be a left join

->leftJoin('tblpurchasebill as tb','tb.fldreference','t.fldreference')

Or you need to add a groupBy

This issue has been fixed by adding 'DISTINCT'

->selectRaw("SUM(DISTINCT(tb.fldcredit+tb.fldtotaltax+tb.fldtotalvat+tb.cccharge)-tb.fldlastdisc)  as total")

It looks like the query you've written is correctly calculating the total sum of the desired columns, but the result is being returned as double the expected value.

One possible cause of this issue is that the columns being summed in the "SUM" function are being summed twice. In other words, each column is being summed twice, which is causing the total sum to be double the expected value.

Another possible cause of this issue could be that the data in the database is already duplicate and you are summing it twice.

You can try to check if any of the above is the cause by adding a group by clause to your query. This will group the results by the columns you specify, which should prevent the data from being summed twice.

$total = DB::table('tblpurchase as t')
        ->join('tblpurchasebill as tb','tb.fldreference','t.fldreference')
        ->selectRaw("SUM((tb.fldcredit+tb.fldtotaltax+tb.fldtotalvat+tb.cccharge)-tb.fldlastdisc)  as total")
        ->whereDate('tb.fldpurdate', '>=', $from_date)
        ->whereDate('tb.fldpurdate', '<=', $to_date)
        ->when($supplier, function ($query, $supplier) {
            $query->where('t.fldsuppname', $supplier);
        })
        ->when($bill, function ($query, $bill) {
            $query->where('t.fldreference', $bill);
        })
        ->when($department, function ($query, $department) {
            $query->where('t.fldcomp', $department);
        })
        ->when($fldbillno, function ($query, $fldbillno) {
            $query->where('t.fldbillno', $fldbillno);
        })->where('t.fldsav',0)
        ->groupBy('t.fldreference')
        ->first();

You can also try to add a distinct clause to the select statement. This will only select unique rows from the result set.

$total = DB::table('tblpurchase as t')
        ->join('tblpurchasebill as tb','tb.fldreference','t.fldreference')
        ->selectRaw("SUM(DISTINCT (tb.fldcredit+tb.fldtotaltax+tb.fldtotalvat+tb.cccharge)-tb.fldlastdisc)  as total")
        ->whereDate('tb.fldpurdate', '>=', $from_date)
        ->whereDate('tb.fldpurdate', '<=', $to_date)
        ->when($supplier, function ($query, $supplier) {
            $query->where('t.fldsuppname', $supplier);
        })
        ->when($bill, function ($query, $bill) {
            $query->where('t.fldreference', $bill);
        })
        ->when($department, function ($query, $department) {
            $query->where('t.fldcomp', $department);
        })
        ->when($fldbillno, function ($query, $fldbillno) {
            $query->where('t.fldbillno', $fldbillno);
        })->where('t.fldsav',0)
        ->groupBy('t.fldreference')
        ->first();