I want to get all the purchases and their sums and also I don't want to add the amount if payments.deleted_at is not null.
Here are the tables
id | name 1 | Gamerzone Book 2 | Recipe Book 3 | EngineX Book
id | purchase_id | amount | deleted_at 1 1 100 2015-06-12 11:00:00 2 2 50 NULL 2 2 10 NULL
$query = DB::table('purchases') ->select(['purchases.*', DB::raw("IFNULL(sum(payments.amount),0) as total") ]) ->leftJoin('payments','payments.purchase_id','=','purchases.id') ->whereNull('payments.deleted_at') ->groupBy('purchases.id')->get();
When I run the code below the 1st result is not included. Result
id | name | total 2 | Recipe Book 60 3 | EngineX Book 0
I know why It is not included but the problem is if I remove whereNull('payments.deleted_at') that particular row in payments will also add to the sum.How should I solve this ??
id | name | total 1 | Gamerzone Book 0 2 | Recipe Book 60 3 | EngineX Book 0