Laravel query with difference of sum of two columns

Question

I have a Laravel query that seems to have some issues. It is picking up all right things but does not calculate the difference of the sum of two fields.

JournalLine::join('accounts','accounts.id','account_id')
->select('accounts.name','accounts.code','accounts.id','accounts.type')
->whereHas('account_fk',function ($query) {
    $query->where('type','Liability');
})
->whereHas('journal_fk',function ($query) {
    $query->where('date', '<=', $this->as_of_date);
})
->selectRaw("SUM(credit) as credit")
->selectRaw("SUM(debit) as debit")
->selectRaw("SUM(credit) - SUM(debit) as amount")
->groupBy('account_id')
->get()->toArray()

It is bringing credit and debit values correct. But balance is wrong. There is something wrong in this - selectRaw("SUM(credit) - SUM(debit) as amount") Can someone please advise? I need the balance of the sum of debit and the sum of credit.

Answer 1

Sum of difference between two column in one table

$saleEarning     = Order::where("type","sale")->sum(DB::raw('totalAmt - adminCommision'));


Hope it help you.

Answer 2

You can use it:

JournalLine::join('accounts','accounts.id','account_id') 
->select([
'accounts.name',
'accounts.code',
'accounts.id',
'accounts.type',
"SUM(credit) as credit",
"SUM(debit) as debit",
"(credit - debit) as amount"
])
->whereHas('account_fk',function ($query) {
    $query->where('type','Liability');
})
->whereHas('journal_fk',function ($query) {
    $query->where('date', '<=', $this->as_of_date);
})
->groupBy('account_id')
->get()->toArray()

Answer 3

there is 2 select statement may be you have to do 1 like this

JournalLine::join('accounts','accounts.id','account_id')
->selectRaw('accounts.name , accounts.code,accounts.id,accounts.type,SUM(accounts.credit) - SUM(accounts.debit) as amount')
->whereHas('account_fk',function ($query) {
    $query->where('type','Liability');
})
->whereHas('journal_fk',function ($query) {
    $query->where('date', '<=', $this->as_of_date);
})
->groupBy('account_id')
->get();