I am connecting a table called tbl_order
by the product code: code_unique
, the objective of this table is to add the sales made of that product, for example I have 100 records that have been sold, I do not want to print 100 records, I just want a record accounting for the total sums of that product sold that exactly is in the column quantity
.
The problem is that in the table tbl_stock
I am adding all the repeated records by grouping or by condition GROUP BY
, so far, it is working perfectly. The only detail is that if I only have 4 x
product sales made in the query, it is doubling that value instead of showing 4
that it is correct, it shows me 8
the wrong value.
This is my query:
SELECT sp.id_stock_product,
ent.establishment,
bo.establishment,
ent.tradename,
bo.tradename,
sp.code_unique,
sp.model,
sp.size,
sp.color,
SUM(sp.item_total) as item_total,
SUM(odr.quantity) as total_sales
FROM tbl_stock_product sp
LEFT JOIN tbl_access ac ON sp.id_enterprise = ac.id_enterprise OR sp.id_branch_office = ac.id_branch_office
LEFT JOIN tbl_enterprise ent ON sp.id_enterprise = ent.id_enterprise
LEFT JOIN tbl_branch_office bo ON sp.id_branch_office = bo.id_branch_office
LEFT JOIN tbl_order odr ON sp.code_unique = odr.code_unique AND sp.id_enterprise = odr.id_enterprise OR sp.id_branch_office = odr.id_branch_office
WHERE ac.id_user = 2
GROUP BY sp.id_enterprise, sp.id_branch_office, sp.code_unique, sp.model, sp.size, sp.color DESC LIMIT 0, 10
How do I prevent these values from being duplicated in this column specifically SUM(odr.quantity) AS total_sales
?
What am I doing wrong?
Registered tables in the following online compiler:
https://www.db-fiddle.com/f/q3iEtNW28frgwETSgVV6ZQ/0
In a summary this would be the expected correct data:
code_unique | model | size | color | item_total | total_sales |
---|---|---|---|---|---|
HELLO | null | null | null | 2010 | 4 |
Note: I excluded the other tables in the example, it does not mean that I am not using it, but to be precise in the expected results add only these columns here SO.
The table
tbl_stock_product
has this data:And the table
tbl_order
has this data:When doing the
join
between the two tables:The result is this:
The previous result is correct since it is observed that for each record of the table
tbl_stock_product
there are three records that match in the tabletbl_order
.To reach the expected result, it can be done in two steps:
quantity
and create a partition by the fieldid_stock_product
to take a unique value for each group.item_total
.Step 1
The result is this:
The column is now resolved
quantity
.Step 2
Result: