0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Drupal developers and administrators

declare @table table
( 
day date,
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('2019-09-02',90, 'alex', 20, '9', '8')
insert @table values('2019-09-02',50, 'corey',20, '9', '8')
insert @table values('2019-09-02',10, 'sima', 20, '9', '8')
insert @table values('2019-09-02',25, 'pete', 20, '9', '7')
insert @table values('2019-09-03',15, 'pete', 20, '5', '6')
insert @table values('2019-09-04',15, 'paul', 20, '5', '6')
insert @table values('2019-09-04',10, 'pete', 56, '6','2')
insert @table values('2019-09-02',15, 'nami', 10, '7','3')
insert @table values('2019-09-04',30, 'John', 10, '7','1')
insert @table values('2019-09-03',20, 'John', 10, '1','1')

SELECT
*,
SUM(mark) OVER (PARTITION BY salecategory ORDER BY name ROWS unbounded PRECEDING) as part_rows
,SUM(mark) OVER (PARTITION BY salecategory ORDER BY name range UNBOUNDED PRECEDING) as part_range
,SUM(mark) OVER (ORDER BY name rows UNBOUNDED PRECEDING) as order_rows
,SUM(mark) OVER (ORDER BY name range UNBOUNDED PRECEDING) as order_range
,SUM(mark) OVER (ORDER BY day rows between 2 preceding and current row) as order2_range
--,SUM(mark) OVER (ORDER BY day range between '2 days' 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 salecategory,name,day

fork query

edited aug 25 19

created jun 2 19 anonymous