I want to do subtraction between entotalitem and exotalitem, the query I am using is to retrieve data from the same table tbl_orders_data.
I tried creating 2 queries, the first one to retrieve entotalitem and the second one to retrieve exotalitem
$encheck = DB::table('tbl_orders_data') ->select('slot_id', DB::raw('sum(total_item) as entotalitem')) ->where('id_order_data', 'like', 'PBM' . '%') ->groupBy('slot_id') ->pluck('entotalitem'); $excheck = DB::table('tbl_orders_data') ->select('slot_id', DB::raw('sum(total_item) as extotalitem')) ->where('id_order_data', 'like', 'PBK' . '%') ->groupBy('slot_id') ->pluck('extotalitem'); $en = $encheck; $ex = $excheck; dd($en - $ex);
Do I only need to use one query? Or should I do 2 queries like I tried? Please help me, thank you
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
You can use conditional aggregation here:
$check = DB::table('tbl_orders_data') ->select('slot_id', DB::raw("sum(case when id_order_data like 'PBM%' then total_item else 0 end) - sum(case when id_order_data like 'PBK%' then total_item else 0 end) as totalitem")) ->groupBy('slot_id') ->pluck('totalitem');