At the moment My head is bang on the wall as I have used few of dba.stackexchanged.com example but none of them is working.
AS I'm trying to get SKU that it sold on each Country, as I have 5 country table that it sold.
When I get results and it seems working fine, but the calculation seems wrong
One of the SKU in UK "H-ROOT-M012R-B" in return at QTY_sold_in_uk is '15552' which this calculation are wrong, the correct one is '8' which QTY has sold not '15552'
Here is a code I wrote this https://www.db-fiddle.com/f/u7PxDXy8kzSHV5n8VHC3rY/1
but I keep getting when I use this https://www.db-fiddle.com/f/qENxYhrqhMHvUMFzYoK4ho/3
When I decided to look around and explore in the StackOverflow and I have used this link pages but results of numbers is keeping return same as the wrong one
I have used inner, left and right join or union seems that calculation is never right. Here is DB Fiddle I has worked on it Example code
I know this code is a long one but please bear with me,
SELECT ti.sku, ti.quantity, -- UK COALESCE((suo_uk.sku), 'Not Sold') AS Unit_in_uk, COALESCE(sum(suo_uk.units_ordered), 0) AS QTY_sold_in_uk, -- De COALESCE((suo_de.sku), 'Not Sold') AS Unit_in_de, COALESCE(sum(suo_de.units_ordered), 0) AS QTY_sold_in_de, -- Fr COALESCE((suo_fr.sku), 'Not Sold') AS Unit_in_fr, COALESCE(sum(suo_fr.units_ordered), 0) AS QTY_sold_in_fr, -- ES COALESCE((suo_es.sku), 'Not Sold') AS Unit_in_es, COALESCE(sum(suo_es.units_ordered), 0) AS QTY_sold_in_es, -- It COALESCE((suo_it.sku), 'Not Sold') AS Unit_in_it, COALESCE(sum(suo_it.units_ordered), 0) AS QTY_sold_in_it FROM tbl_inventory AS ti LEFT JOIN tbl_sku_units_order AS suo_uk ON suo_uk.sku = ti.sku LEFT JOIN tbl_sku_units_order_de AS suo_de ON suo_de.sku = ti.sku LEFT JOIN tbl_sku_units_order_fr AS suo_fr ON suo_fr.sku = ti.sku LEFT JOIN tbl_sku_units_order_es AS suo_es ON suo_es.sku = ti.sku LEFT JOIN tbl_sku_units_order_it AS suo_it ON suo_it.sku = ti.sku -- where ti.sku=" H-ROOT-M012R-B" GROUP BY ti.sku
Please, Advice and your advice would be greatly appreciated.