DB-Fiddle

CREATE TABLE inventory (
    id SERIAL PRIMARY KEY,
    stock_date DATE,
    product VARCHAR(255),
    inbound_quantity INT,
    outbound_quantity INT
);

INSERT INTO inventory
(stock_date, product, inbound_quantity, outbound_quantity
)
VALUES 
('2020-01-01', 'Product_A', '900', '0'),
('2020-01-02', 'Product_A', '0', '300'),
('2020-01-03', 'Product_A', '400', '250'),
('2020-01-04', 'Product_A', '0', '100'),
('2020-01-05', 'Product_A', '700', '500'),
  
('2020-01-03', 'Product_B', '850', '0'),
('2020-01-08', 'Product_B', '100', '120'),
('2020-02-20', 'Product_B', '0', '360'),
('2020-02-25', 'Product_B', '410', '230'),

Expected Result:

I want to calculate the balance per product.
So far I have been able to develop this query below but it does not work.
I get error window "product" does not exist.

SELECT 
iv.stock_date AS stock_date,
iv.product AS product,
iv.inbound_quantity AS inbound_quantity,
iv.outbound_quantity AS outbound_quantity,
SUM(iv.inbound_quantity - iv.outbound_quantity) OVER 
(product ORDER BY stock_date ASC ROWS UNBOUNDED PRECEDING) AS Balance
FROM inventory iv
GROUP BY 1,2,3,4
ORDER BY 2,1;

How do I need to modify the query to make it work?

CodePudding user response：

You are almost there

You should add partition by in front of product

SELECT 
iv.stock_date AS stock_date,
iv.product AS product,
iv.inbound_quantity AS inbound_quantity,
iv.outbound_quantity AS outbound_quantity,
SUM(iv.inbound_quantity - iv.outbound_quantity) OVER 
(partition by product ORDER BY stock_date ASC ROWS UNBOUNDED PRECEDING) AS Balance
FROM inventory iv
GROUP BY 1,2,3,4
ORDER BY 2,1;

So, it should be like this

CodePudding user response：

The error message "product" does not exist is because you are trying to reference the column "product" in the OVER clause, but it is not included in the GROUP BY clause.

To fix this issue, you will need to include the "product" column in the GROUP BY clause, and also add a partition by clause to the OVER clause, so that the SUM function will calculate the balance per product.

Try this query:

SELECT 
    iv.stock_date AS stock_date,
    iv.product AS product,
    iv.inbound_quantity AS inbound_quantity,
    iv.outbound_quantity AS outbound_quantity,
    SUM(iv.inbound_quantity - iv.outbound_quantity) OVER 
    (PARTITION BY product ORDER BY stock_date ASC ROWS UNBOUNDED PRECEDING) AS Balance
FROM inventory iv
GROUP BY 1,2,3,4
ORDER BY 2,1;

This way, SUM function will only sum the inbound_quantity - outbound_quantity for each product, and not for all the products.

The query will return the expected result.