1

i'm trying to do LEFT JOIN with SUM and Arithmetic Operators of 3 tables, and i'm stuck,

In the expected result in the bottom below, there is new field called initial_stock which is the result of:

initial_stock = current_stock + sum(used) - sum(restock_amount)

You can try in in here: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=2216e64ee6fa90556d8f952f115dd070

Heres the tables:

product table:

id      product_name  current_stock    
------  ------------  -------------  
  1     abc           10  
  2     aaa           0  
  3     bbb           10  
  4     ddd           20

usage table:

id      product_id    used     date_out
------  ------------  -------  ----------
  1     1             10       2020-11-20
  2     1             20       2020-11-20
  3     2             20       2020-11-11

product_restock table:

id      product_id    restock_amount  date_in
------  ------------  --------------  -----------
  1     1             10              2020-11-15
  2     1             20              2020-11-14
  3     4             10              2020-11-09

Expected result from date 2020-11-01 to 2020-11-30:

id      product_name  initial_stock restock used current_stock
------  ------------  ------------- ------- ---- -------------
  1     abc             10          30      30   10
  2     aaa             20          0       20   0
  3     bbb             10          0       0    10    
  4     ddd             10          10      0    20
Diand
  • 878
  • 7
  • 12

2 Answers2

1

Aggregate in usage and product_restock separately and then join to product:

select p.id, p.product_name,
       p.current_stock  + coalesce(u.used, 0) - coalesce(r.restock, 0) initial_stock,
       coalesce(r.restock, 0) restock,
       coalesce(u.used, 0) used,
       p.current_stock
from product p
left join (select product_id, sum(used) used from usage where date_out between '2020-11-01' and '2020-11-30' group by product_id) u
on u.product_id = p.id
left join (select product_id, sum(restock_amount) restock from product_restock where date_in between '2020-11-01' and '2020-11-30' group by product_id) r
on r.product_id = p.id

See the demo.
Results:

> id | product_name | initial_stock | restock | used | current_stock
> -: | :----------- | ------------: | ------: | ---: | ------------:
>  1 | abc          |            10 |      30 |   30 |            10
>  2 | aaa          |            20 |       0 |   20 |             0
>  3 | bbb          |            10 |       0 |    0 |            10
>  4 | ddd          |            10 |      10 |    0 |            20
forpas
  • 160,666
  • 10
  • 38
  • 76
  • 1
    Working as expected! Thanks!! – Diand Dec 05 '20 at 10:49
  • i forgot, you need add ```where``` date filter, to the answer, so i can mark your answer as accepted. something like this: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=9a95e58186d918596e594156e54a255b maybe? @forpas – Diand Dec 06 '20 at 04:33
  • @Diand yes you can set any conditions you need in the subqueries, but does this return the correct initial_stock if by initial_stock you mean the stock right before the period you search for? I think that the correct formula in this case is: `initial_stock = (current_stock at the end of that period) + (sum(used) of that period) - (sum(restock_amount) of that period)`. – forpas Dec 06 '20 at 09:12
  • Hmm, i also not sure about this, can you create the 2nd option for that possible correct answer? the current answer is not throwing any error, but i think i need to test it out with the option you think are the correct formula with more data.. – Diand Dec 07 '20 at 10:08
  • Without more data this can't be tested and of course you have to know your expected results. – forpas Dec 07 '20 at 13:11
0

You can can use lateral joins for this:

select p.*, u.used, pr.restock_amount, 
    p.current_stock + u.used - pr.restock_amount as initial_stock
from product p
cross join lateral (
    select coalesce(sum(u.used), 0) as used
    from usage u 
    where u.product_id = p.id and u.date_out between '2020-11-01'::date and '2020-11-30'::date
) u
cross join lateral (
    select coalesce(sum(u.restock_amount), 0) as restock_amount
    from product_restock pr 
    where pr.product_id = p.id and pr.date_out between '2020-11-01'::date and '2020-11-30'::date
) pr
GMB
  • 216,147
  • 25
  • 84
  • 135