CODEWITHSUNDEEP
sqlpostgresqlsumleft-joinlateral-join
Diand
Diand

Reputation: 888

PostgreSQL LEFT JOIN with SUM & Arithmetic Operators

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

Upvotes: 1

Views: 1154

Answers (2)

forpas
forpas

Reputation: 164089

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

Upvotes: 2

GMB
GMB

Reputation: 222462

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

Upvotes: 0

Related Questions