Q&A for gardeners and landscapers
declare @table table ( date datetime, mark int, name varchar(10), cost int, salecategory int, costcategory int) insert @table values('2019-09-01',10,'pete', 20, '9', '8') --insert @table values(90, 'alex', 20, '9', '8') --insert @table values(50, 'corey',20, '9', '8') --insert @table values(10, 'sima', 20, '9', '8') --insert @table values(25, 'pete', 20, '9', '7') --insert @table values(15, 'pete', 20, '5', '6') --insert @table values(15, 'paul', 20, '5', '6') --insert @table values(10, 'pete', 56, '6','2') --insert @table values(15, 'nami', 10, '7','3') --insert @table values(30, 'John', 10, '7','1') --insert @table values(20, 'John', 10, '1','1') SELECT *, SUM(mark) OVER (PARTITION BY salecategory ORDER BY name ROWS unbounded PRECEDING) as part_rowsunboundedp ,SUM(mark) OVER (PARTITION BY salecategory ORDER BY name range UNBOUNDED PRECEDING) as part_rangeunboundedp ,SUM(mark) OVER (ORDER BY name rows UNBOUNDED PRECEDING) as order_range ,SUM(mark) OVER (ORDER BY name range UNBOUNDED PRECEDING) as order_range ,SUM(mark) OVER (ORDER BY name rows between 2 preceding and current row) as order2_range --range - if order by and partition is same- auto adds up in that particular row from @table order by name