Skip to content
Advertisement

Is it possible to do a several calculations in partition by statement in SQL?

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

enter image description here

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
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement