Group by returns wrong sum

Here’s my 3 tables:

Products table: id, product_name
Purchase Product table: id, product_id, purchase_price, quantity
Sales Product table: id, product_id, sales_price, quantity, purhase_price.

I want to find the products which is in purchase list as well as sales list . If it’s not it sales list it should return null sales value as well as quantity . But with group by it’s showing wrong sum. What could be possible error of my query.

Here’s my query:

$products = DB::table('products')
        ->join('purchase_products','purchase_products.product_id','products.id')
         ->leftjoin("sales_products",function($join){
    $join->on("sales_products.purchase_price","purchase_products.purchase_price")
         ->on("sales_products.product_id","purchase_products.product_id");
    })
        ->select('products.product_name','purchase_products.purchase_price',DB::raw("SUM(purchase_products.quantity) as purchase_quantity"),'sales_products.sales_price',DB::raw("SUM(sales_products.quantity) as sales_quantity"))
  
        ->groupby('products.id','purchase_products.purchase_price')
        ->get();

enter image description here

Source: Laravel

Leave a Reply