To define a single (named) window and compute multiple function results over it:
SELECT p.ID, p.Description, s.quantity, s.ShipDate,SUM(s.Quantity) OVER ws1, MIN(s.quantity) OVER ws1FROM SalesOrderItems s JOIN Products p ON (s.ProductID =p.ID) WHERE s.ShipDate BETWEEN '2000-01-09' AND'2000-01-17' AND s.Quantity > 40 window ws1 AS(PARTITION BY productid ORDER BY shipdate rowsbetween unbounded preceding and current row)ORDER BY p.id;
The results from the above query:
ID description quantity ship_date sum min --- ----------- -------- ----------- --- --- 400 Cotton Cap 48 2000-01-09 48 48 401 Wool cap 48 2000-01-09 48 48 500 Cloth Visor 60 2000-01-14 60 60 500 Cloth Visor 60 2000-01-15 120 60 501 Plastic Visor 60 2000-01-14 60 60
