Unexpected Output Error: Arithmetic overflow error converting expression to data type int-CodePudding

--Looking At Total Population vs Vaccinations

SELECT dea.continent, dea.location, dea.population, dea.Date
    , vac.new_vaccinations
    , SUM(CAST(vac.new_vaccinations AS int)) OVER (Partition BY dea.location ORDER BY dea.location, dea.Date) AS RollingPopVaccinated
FROM PortfolioProject..CovidDeaths dea
JOIN PortfolioProject..CovidVaccination vac
    ON dea.location = vac.location
    AND dea.Date = vac.Date
WHERE dea.continent IS NOT NULL

OUTPUT:

Msg 8115, Level 16, State 2, Line 70 Arithmetic overflow error converting expression to data type int. Warning: Null value is eliminated by an aggregate or other SET operation.

CodePudding user response：

Try casting new_vaccinations to bigint instead of regular int:

SELECT dea.continent, dea.location, dea.population, dea.Date, vac.new_vaccinations,
       SUM(CAST(vac.new_vaccinations AS bigint)) OVER (PARTITION BY dea.location
           ORDER BY dea.location, dea.Date) AS RollingPopVaccinated
FROM PortfolioProject..CovidDeaths dea
INNER JOIN PortfolioProject..CovidVaccination vac
    ON dea.location = vac.location AND dea.Date = vac.Date
WHERE dea.continent IS NOT NULL;