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();

Could you add the expected vs the actual result to your question? — shaedrich, Jun 22 '21 at 13:32
Why do you group by `'purchase_products.purchase_price'` but not by `'sales_products.purchase_price'`? — shaedrich, Jun 22 '21 at 13:34
What happens if you add `'sales_products.purchase_price'` to your `groupBy`? — shaedrich, Jun 22 '21 at 13:53
Why are you applying the join condition on price fields, is it needed. "$join->on("sales_products.purchase_price","purchase_products.purchase_price")" — Ashok Kumar, Jun 22 '21 at 14:46
because same product has different different purchase price an i need to track which purchase price has been sold. thanks — Hola, Jun 22 '21 at 14:49
Please try with this raw query in your mysql client " SELECT p.product_name, pr.purchase_price, SUM(pr.quantity) as purchase_quantity, ps.sales_price, SUM(ps.quantity) as sales_quantity FROM products p JOIN purchase_products pr ON p.id = pr.product_id LEFT JOIN product_sales ps ON ( pr.product_id = ps.product_id AND pr.purchase_price=ps.purchase_price) GROUP BY p.id, pr.purchase_price, ps.purchase_price" — Ashok Kumar, Jun 22 '21 at 15:01
i have tried with your raw query but same output i'm getting thanks. @AshokKumar — Hola, Jun 22 '21 at 15:08
Please use text, not images/links, for text--including tables & ERDs. [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. Please clarify via edits, not comments. — philipxy, May 14 '22 at 21:11
Please in code questions give a [mre]--cut & paste & runnable code & example input; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, May 14 '22 at 21:12
Possible duplicate of [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/q/12464037/3404097) — philipxy, May 14 '22 at 21:13

score 0 · Answer 1 · answered Jun 22 '21 at 15:31

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.

likely not today. and it would be much easier if you would provide your table structure (edit your question to show output of `show create table yourtablename` for all tables, as text, not an image) and insert statements to create sample data — ysth, Jun 22 '21 at 16:12

Group by returns wrong sum

1 Answers1