I want to do the following calculation in SQL Microsoft server,
(sum(value)/avg(Numberofmonths))*(avg(Numberofmonths)/12)) over (partition by year,Country) as totalsales
As I understand all variables are aggregated by partition by the statement, however, I got an error Incorrect syntax. How can I solve the problem?
Data is following and the output should be as in output column
Advertisement
Answer
Each window function needs an over
clause:
((sum(value) over (partition by year, Country) / avg(Numberofmonths) over (partition by year, Country) ) * (avg(Numberofmonths) over (partition by year, Country) / 12) ) as totalsales
Or simplify this to:
(sum(value) over (partition by year, Country) / 12) ) as totalsales