Postgresql Joining tables without losing records

user_7823702 0
Viewed 55

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.

join postgresql sql
2 Answers

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)

Demo

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.

user_18632290

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.

user_11440350
Related