ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW on sum column

Question

how can i add
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW on sum column? because the lead/lag function dont work

used the cod :

select year (orderdate),month (orderdate),sum (SubTotal),
lead ( sum (SubTotal),0) OVER ( PARTITION BY sum (SubTotal) ORDER BY sum (SubTotal) ) as PrevOrder
from sales.SalesOrderHeader
where orderdate <= '2011-12-31'
group by
grouping sets
((year (orderdate),month (orderdate)), () )

Answer 1

Hi @stasv85

because the lead/lag function dont work

The reason is that you set lead offset = 0.
Basic Syntax :

LAG (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause )

Have a change like this : lag ( sum (salary),1,0) OVER ( ORDER BY sum (salary)) as PrevOrder
For more details about LAG , please refer to this document: LAG (Transact-SQL)

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @stasv85

how can i add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW on sum column?

Try this:

select year (orderdate) [Year],month (orderdate) [Month],sum (SubTotal) [Sum_price],   
       sum (sum (SubTotal))over(partition by year (orderdate) order by year (orderdate),month (orderdate)  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )[Money]   
from sales.SalesOrderHeader   
where orderdate <= '2011-12-31'   
group by grouping sets   
((year (orderdate),month (orderdate)))   

If it is not working or helpful, please provide more sample data (CREATE TABLE …)(INSERT INTO …) or details about your issue.
Please refer to this article for some examples:https://learnsql.com/blog/sql-window-functions-rows-clause/
Best regards,
LiHong