Fix invalid datatype error, nvarchar is invalid [duplicate]

Question

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.

How to fix it? Well, if you want to store numerical values - then use a numerical datatype - int, decimal or something - NOT nvarchar !! — marc_s
– marc_s, Commented Apr 28, 2023 at 18:51
Thanks for the comment. Could you clarify on how I would begin to do that? — JC234
– JC234, Commented Apr 28, 2023 at 18:55
Try 100 * CAST(total_deaths AS Integer) / CAST(total_cases AS Integer) — Bohemian
– Bohemian ♦, Commented Apr 28, 2023 at 18:55
I'm getting a similar error again for another query with it saying even though I used CAST As Integer: Arithmetic overflow error converting expression to data type int. Does this mean I cannot CAST as Integer? — JC234
– JC234, Commented Apr 28, 2023 at 19:22
You might need bigint - things like total vaccinations in China would overflow an int. But you should change the datatype of the columns in the table to an appropriate numeric type. Not cast in the queries — Martin Smith
– Martin Smith, Commented Apr 28, 2023 at 19:54

Paul Maxwell · Accepted Answer · 2023-04-29 07:25:32Z

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:

SELECT
      Location
    , [date]
    , total_cases
    , total_deaths
    , (TRY_CAST(total_deaths AS INT) / TRY_CAST(total_cases AS INT)) * 100.0 AS DeathPercentage
FROM PortfolioProject.dbo.CovidDeaths$
ORDER BY
      Location
    , [date]

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:

SELECT
      Location
    , [date]
    , total_cases
    , total_deaths
    , (TRY_CAST(total_deaths AS NUMERIC(10, 2)) / NULLIF(TRY_CAST(total_cases AS NUMERIC(10, 2)), 0)) * 100.0 AS DeathPercentage
FROM PortfolioProject.dbo.CovidDeaths
ORDER BY
      Location
    , [date]

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.

-- Step 1: Create a backup copy of the table
SELECT * INTO PortfolioProject.dbo.CovidDeaths_backup
FROM PortfolioProject.dbo.CovidDeaths;

-- Step 2: Add new numeric columns to the table
ALTER TABLE PortfolioProject.dbo.CovidDeaths
ADD total_cases_numeric NUMERIC(10,2),
    total_deaths_numeric NUMERIC(10,2);

-- Step 3: Set the values of the new columns using TRY_CAST
UPDATE PortfolioProject.dbo.CovidDeaths
SET total_cases_numeric = TRY_CAST(total_cases AS NUMERIC(10,2)),
    total_deaths_numeric = TRY_CAST(total_deaths AS NUMERIC(10,2));


-- WARNING you may want to stop at this point! Inspect the new columns before proceeding

-- Step 4: OPTIONAL! Drop the original nvarchar columns
ALTER TABLE PortfolioProject.dbo.CovidDeaths
DROP COLUMN total_cases,
            total_deaths;

-- Step 5: OPTIONAL! Rename the new numeric columns to the original names
EXEC sp_rename 'PortfolioProject.dbo.CovidDeaths.total_cases_numeric', 'total_cases', 'COLUMN';
EXEC sp_rename 'PortfolioProject.dbo.CovidDeaths.total_deaths_numeric', 'total_deaths', 'COLUMN';

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.

Collectives™ on Stack Overflow

Fix invalid datatype error, nvarchar is invalid [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related