This question already has an answer here:
Can I use "/" operator on nvarchar column type in SQL Server Management Studio? [closed] (1 answer)
Closed 6 days ago.
I have the query:
Select
Location, date, total_cases, total_deaths,
(total_deaths / total_cases) * 100 as DeathPercentage
From
PortfolioProject.dbo.CovidDeaths$
Order By
1, 2
But when I run it I get this error:
Msg 8117, Level 16, State 1, Line 24
Operand data type nvarchar is invalid for divide operator.
How do I rewrite the query so this error can be fixed?
I used
SELECT
CAST(total_deaths AS Integer)
FROM
PortfolioProject.dbo.CovidDeaths$
SELECT
CAST(total_cases AS Integer)
FROM
PortfolioProject.dbo.CovidDeaths$
and it returned the integers, but when I rerun the query with the division I get the same error.
1条答案
按热度按时间qfe3c7zg1#
As the error message informs us that you are attempting arithmetic on nvarchar columns, you need to cast (or convert) the data into something that you can perform arithmetic with. As you are using SQL Server I recommend you use
TRY_CAST()
which is fault tolerant in that if a value cannot be converted it returns NULL instead of halting the query. So, you could do this:Going a step further you may need to avoid a divide by zero error, and you may also need more precise figures than integers will provide, so perhaps:
ps: "date" isn't a wise choice of column name.
In the comments you ask for help on how to "fix" the nvarchar columns. This involves several steps and you need to make sure you have a backup in case it belly flops at any point.
Don't do any of this column changeover if you are not comfortable with it.
nb: throughout I have used numeric(10,2) that is a guess, you can adjust this to suit, or just use integers if that's what you need.