Group by returns wrong sum

Question

Here are my 3 tables:

Products         : (id, product_name)
Purchase Product : (id, product_id, purchase_price, quantity)
Sales Product    : (id, product_id, sales_price, quantity, purhase_price)

I want to find the products which are on the purchase list as well as the sales list. If it's not on it the sales list it should return NULL for sales value as well as quantity.

Here same product has different different purchase price so I need to track which purchase product has been sold. But with the group by it's showing the wrong sum.

What could be a 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();

Answer 1

When you join multiple tables, what is being summed is every combination of the joined tables. So if you have two sales records for a product, the sum of the distinct purchases will be doubled.

I can't tell you how to do it in laravel, but you can remove your sales join and use products.id in (select product_id from sales_products) instead to tell if a product has a sale, or instead of joining sales_products and purchase_products at the same time, join products and sales_products in a subquery that only returns distinct product ids.

Or if you really don't want to change your query structure, you can just change:

SUM(purchase_products.quantity)

to

SUM(purchase_products.quantity) / GREATEST(1, COUNT(sales_products.id))

By the way, I don't see where in your query you are setting sales to null if the product is not on the sales list.

Also, you probably want to group by purchase_products.id instead of purchase_products.purchase_price, in case a product has the same price twice.