test sql

0

Please login or register to vote for this query.

(click on this box to dismiss)

Gardening and Landscaping

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

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results