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 or 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.