How to fix SQL error "Incorrect syntax near the keyword 'OVER'"

Question

I am getting the following error when running my SQL query:

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'OVER'.

Here is my query:

Select cd.continent, cd.location, cd.date, cd.population, cv.new_vaccinations 
, SUM((CONVERT(bigint,cv.new_vaccinations)) OVER (partition by cd.location order by cd.location, cd.Date) as RollingPeopleVaccinated
From CovidData..CovidDeaths cd
Join CovidData..CovidVaccination CV
    On cd.location = cv.location
    and cd.date = cv.date
Where cd.continent is not null
Order by 2,3  

What can I do to fix this error?

Answer 1

There is an extra ( before sum, it should be:

Select cd.continent, cd.location, cd.date, cd.population, cv.new_vaccinations  , SUM(CONVERT(bigint,cv.new_vaccinations)) OVER (partition by cd.location order by cd.location, cd.Date) as RollingPeopleVaccinated From CovidData..CovidDeaths cd Join CovidData..CovidVaccination CV     On cd.location = cv.location     and cd.date = cv.date Where cd.continent is not null Order by 2,3  

Answer 2

Which SQL Server version are you using?

SELECT @@VERSION

AFAIK the windows aggregation SUM...OVER was first introduced in SQL Server 2016.

Answer 3

Select cd.continent, cd.location, cd.date, cd.population, cv.new_vaccinations

, SUM(CONVERT(bigint,cv.new_vaccinations)) OVER (partition by cd.location order by cd.location, cd.Date) as RollingPeopleVaccinated

From CovidData..CovidDeaths cd

Join CovidData..CovidVaccination CV

On cd.location = cv.location and cd.date = cv.date Where cd.continent is not null

Order by 2,3