Company logo
  • Jobs
  • Bootcamp
  • About Us
  • For professionals
    • Home
    • Jobs
    • Courses
    • Questions
    • Teachers
    • Bootcamp
  • For business
    • Home
    • Our process
    • Plans
    • Assessments
    • Payroll
    • Blog
    • Calculator

0

35
Views
Postgresql Joining tables without losing records

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.

9 months ago · Santiago Trujillo
2 answers
Answer question

0

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.

9 months ago · Santiago Trujillo Report

0

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.

9 months ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post job Plans Our process Sales
Legal
Terms and conditions Privacy policy
© 2023 PeakU Inc. All Rights Reserved.