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

--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.

sqlsql-server


Solution 1:[1]

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;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen

Related Questions