Let's say I have the following tables:
1 - StartingStock:
vendor | starting_stock ------------------------ adidas | 13 Reebok | 5
2 - Restock:
vendor | restocks ----------------- adidas | 2 nike | 3
3 - Sales:
vendor | quantity_sold ---------------------- adidas | 10 nike | 1
I want my resulting table to be the sell through grouped by vendor. In this scenario, sell through is calculated like this:
quantity_sold/(starting_stock + restocks). My only problem is that starting stock and restock tables may not have the some vendors that are present in the sales table. So in the scenario above,
StartingStock does not have
nike as a record. So if that's the case the sell though for nike would be just
1/(3+0). Therefore, my resulting table would be:
vendor | sell_through --------------------- adidas | 1.5 nike | 0.33 Reebok | 0
So I'd want all of the vendors present in the result table (if it has no sales, value is 0 like Reebok shown above).
I tried working with the different types of joins but I couldn't get it. Any help would be great. Thanks.
We can try a full outer join approach here:
SELECT COALESCE(ss.vendor, r.vendor, s.vendor) AS vendor, COALESCE(s.quantity_sold, 0) / (COALESCE(ss.starting_stock, 0) + COALESCE(r.restocks, 0)) AS sell_through FROM StartingStock ss FULL OUTER JOIN Restock r ON ss.vendor = r.vendor FULL OUTER JOIN Sales s ON s.vendor = COALESCE(ss.vendor, r.vendor)
Note that I am coming up with 2/3 for the sell through for Adidas, since the quantity sold is 10, and the sum of stocks is 15.
I would use
union all and aggregation:
select vendor, sum(starting_stock), sum(restock), sum(quantity_sold), (sum(quantity_sold) * 1.0 / sum(starting_stock) + sum(restock)) as sell_through from ((select vendor, starting_stock, 0 as restock, 0 as quantity_sold from startingstock ) union all (select vendor, 0 as starting_stock, restock, 0 as quantity_sold from restock ) union all (select vendor, 0 as starting_stock, 0 as restock, quantity_sold from sales ) ) v group by vendor;
In particular, this version includes each number in the calculation only once. A
JOIN approach will produce inaccurate results if a vendor has multiple rows in any of the tables.