Laravel find difference of sum of same column with difference condition

user_6684758 0
Viewed 186

I have a table with different types of data. I want to find

sum(typeA) - sum(typeB)

Type A = whereIn('type', ['CreditNote','ReceiptVoucher'])

Type B = ->whereIn('type', ['DebitNote','PaymentVoucher'])

This is the query I came up with, but it isn't working. What am I missing?

Transaction::where('contact_id',$this->contact->id)->where('date', '<', $from)
        ->where(function ($query) {
            $query
            ->whereIn('type', ['CreditNote','ReceiptVoucher'])
            ->selectRaw("SUM(amount) as in");
        })
        ->Where(function ($query) {
            $query
            ->whereIn('type', ['DebitNote','PaymentVoucher'])
            ->selectRaw("SUM(amount) as out");
        })
        ->selectRaw("COALESCE(SUM(in),0) - COALESCE(SUM(out),0) as amount")
        ->groupBy('contact_id')
        ->get();

If I can't find a difference like this, I could also just get the two sums and I can subtract them later.

eloquent laravel
1 Answers

You could use raw query to get this, you just need to specify the WHERE statement for date and contact_id, then for the SUM statement, put the type conditions inside the IF statement like this:

SELECT 
    SUM(// TYPE CONDITIONS FOR IN, Quantity, 0)) - SUM(IF(// TYPE CONDITIONS FOR IN, Quantity, 0)) as total
    FROM x
    WHERE ...
    GROUP BY ...;

After you've tried the raw query, use DB::raw to execute it.

Note: somehow in and out that you used for the aliases were SQL's keywords, so change it to inQty and outQty or whatever.

SELECT SUM(IF(OrderDetailId <= 5, Quantity, 0)) as inQty, SUM(IF(OrderDetailId > 5 AND OrderDetailId <= 10, Quantity, 0)) as outQty, SUM(IF(OrderDetailId <= 5, Quantity, 0)) - SUM(IF(OrderDetailId > 5 AND OrderDetailId <= 10, Quantity, 0)) as total FROM OrderDetails;

user_125433200
Related