Calculate balance amount in redshift

DB-Fiddle

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

INSERT INTO inventory
(stock_date, inbound_quantity, outbound_quantity
)
VALUES 
('2020-01-01', '900', '0'),
('2020-01-02', '0', '300'),
('2020-01-03', '400', '250'),
('2020-01-04', '0', '100'),
('2020-01-05', '700', '500');

Expected Output:

stock_date inbound_quantity outbound_quantity balance
2020-01-01 900 0 900
2020-01-02 0 300 600
2020-01-03 400 250 750
2020-01-04 0 100 650
2020-01-05 700 500 850

Query:

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

With the above query I am able to calculate the balance of inbound_quantity and outbound_quantity in PostgresSQL.
However, when I run the same query in Amazon-Redshift I get this error:

Amazon Invalid operation: Aggregate window functions with an ORDER BY clause require a frame clause;
1 statement failed.

How do I need to change the query to also make it work in Redshift?

>Solution :

As the error speaks, you need to add the frame specification clause, namely the ROWS UNBOUNDED PRECEDING into your window function.

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

Leave a Reply