Postgresql SUM and UPDATE

Question

I want to ask about SUM and then UPDATE it in Postgresql DB.
In my case I want to sum (units) in stockdiary table and then do the deduction/subtract with table products (stockvolume).

I was able to sum the units in stockdiary table:

     SELECT NAME,SUM(UNITS) AS MINUS
     FROM STOCKDIARY
     INNER JOIN PRODUCTS
     ON PRODUCTS.ID = STOCKDIARY.PRODUCT
     GROUP BY NAME

     result =>
     name             ||    minus
     USB CABLE 1M     ||    -8  
     USB CHARGER      ||    -3

then the problem is how to subtract minus and stockvolume and update it back to the products table?

stockdiary table
id(pk),datenew,product,units,price

products table
name,id(pk),pricebuy,pricesell,stockvolume

@Vivek.S thanks it works.

    UPDATE products
    SET stockvolume = sub.minus
    FROM (SELECT
    SUM(UNITS) AS MINUS,PRODUCTS.ID
    FROM STOCKDIARY
    INNER JOIN PRODUCTS ON PRODUCTS.ID = STOCKDIARY.PRODUCT
    GROUP BY NAME) sub
    WHERE sub.productid = products.productid

@MattiasLindberg iam sorry please make me know if my question are not clear. — irebmy
– irebmy, Commented Nov 13, 2015 at 7:25

LongBeard_Boldy · Accepted Answer · 2015-11-13 09:12:39Z

3

 update  products  set stockvolume = stockvolume - diary.minus from 
(SELECT  STOCKDIARY.product,SUM(UNITS) AS MINUS
 FROM STOCKDIARY
 INNER JOIN PRODUCTS
 ON PRODUCTS.ID = STOCKDIARY.PRODUCT
 GROUP BY STOCKDIARY.product) as diary where products.id= diary.product

dont use reserved words as tablenames, column names and etc ... :)

answered Nov 13, 2015 at 9:12

LongBeard_Boldy

9128 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

irebmy Over a year ago

its solve my proble .. but i must change "stockvolume + diary.minus) because my db already "-" for the stock volume..thank you

Collectives™ on Stack Overflow

Postgresql SUM and UPDATE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related