当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - "Incorrect syntax" error using OVER()

问题描述:

I have a sales budget for every trading day of the month. So for day 1 the budget is 300, for day 2 the budget is 400, and then month to date budget is 700. I get this error in my query: Incorrect syntax near 'ROWS'.

select

TradingDate

,Budget

,sum(Budget) over (PARTITION BY TradingDate

order by TradingDate asc

ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),1) AS BudgetMTD

from #4

网友答案:

Your query is taking the sum of each day's amount, with the next amount within the same day (your partition and order by are on the same field). This is allowed, but it meanns one of three things:

  1. The TradingDate is a date, with multiple values on each date. If so, the sum is indeterminate, since you do not know which one would follow.
  2. TradingDate is poorly named, and it is really a date time. In this case, you are getting the next date time value, on the same date.
  3. Your query is misformed and you do not really mean "partition by TradingDate order by TradingDate".

I would speculate that you want one of two things. The first would be the sum of one day's Budget with the next day's. The other would be a cumulative sum.

There is the possibility that you have multiple rows per day and you want the sum of the budget on that day. If that's the case, then you can use the simpler formulation:

select TradingDate, Budget,
       sum(Budget) over (PARTITION BY TradingDate) AS BudgetMTD
from #4
网友答案:

Ok I came up with a subquery solution:

select TradingDate, Budget,
RunningTotal = (select sum(Budget) from #4 s2
where s2.TradingDate<= s1.TradingDate)
from #4 s1
order by s1.TradingDate
分享给朋友:
您可能感兴趣的文章:
随机阅读: