Subtract constant across database tables

Question

I need to subtract a value, found in a different table, from values across different rows. For example, the tables I have are:

ProductID | Warehouse | Locator | qtyOnHand
-------------------------------------------
100       | A         | 123     | 12
100       | A         | 124     | 12
100       | A         | 124     | 8
101       | A         | 126     | 6
101       | B         | 127     | 12

ProductID | Sold
----------------
100       | 26
101       | 16

Result:

ProductID | Warehouse | Locator | qtyOnHand | available
-------------------------------------------------------
100       | A         | 123     | 12        | 0
100       | A         | 123     | 12        | 0
100       | A         | 124     | 8         | 6
101       | A         | 126     | 6         | 0 
101       | B         | 127     | 12        | 12

The value should only be subtracted from those in warehouse A. Im using postgresql. Any help is much appreciated!

SQL tables represent *unordered* sets. Your results seem to presuppose an ordering but no column specifies the ordering. Why is the fourth row "0" rather than "6"? Why is the last row "12" and not "4"? — Gordon Linoff, Jul 30 '20 at 12:19
I manually created example data, there is no real order. The fourth row is should be 0 as it is a different product (of which there have been 16 sales) and the final row is 12 as only products stored in warehouse A are affected. — RozzaT, Jul 30 '20 at 12:49
I thing you should more clearly explain how the records of your second table is to effect the result. — Scratte, Jul 30 '20 at 13:15

Gordon Linoff · Accepted Answer · 2020-07-30T14:30:32.410

1

If I understand correctly, you want to compare the overall stock to the cumulative amounts in the first table. The rows in the first table appear to be ordered from largest to smallest. Note: This is an interpretation and not 100% consistent with the data in the question.

Use JOIN to bring the data together and then cumulative sums and arithmetic:

select t1.*,
       (case when running_qoh < t2.sold then 0
             when running_qoh - qtyOnHand < t2.sold then (running_qoh - t2.sold)
             else qtyOnHand
        end) as available
from (select t1.*,
             sum(qtyOnHand) over (partition by productID order by qtyOnHand desc) as running_qoh
      from table1 t1
     ) t1 join
     table2 t2
     using (ProductID)

edited Jul 30 '20 at 14:30

answered Jul 30 '20 at 12:21

Gordon Linoff

1,242,037
58
646
786

1

This works perfect thank you. Only thing I had to do was change the THEN to `running_qoh - sold` instead of `t2.sold - (running_qoh - qtyOnHand)` – RozzaT Jul 30 '20 at 13:19
@RozzaT . . . I fixed the answer. – Gordon Linoff Jul 30 '20 at 14:30

Subtract constant across database tables

1 Answers1