-3

I am trying to divide 2 columns which are defined as nvarchar, but SSMS throws an error saying you can not use / operator on nvarchar.

select 
    location, date, total_cases, total_deaths, 
    (total_deaths / total_cases) * 100
from 
    CovidDeaths#xlsx$
order by 
    1, 2

I am unable to divide the total_cases and total_deaths.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jayesha Miglani
  • 1
  • 2
  • 2
    Of course you can't divide two text values. You might just as well try to divide "foo" / "bar". But you could try `cast()`-ing the values to some numeric type first. – Joel Coehoorn Mar 20 '23 at 19:16
  • Also, telling us you use Management Studio is like telling us the car you drive is a Sony, because that's the brand you see on the dashboard radio. Fine some of the time, but less helpful when talking to your mechanic about engine trouble. – Joel Coehoorn Mar 20 '23 at 19:17
  • 1
    This is literally the 3rd time I have seen this question in less than 24 hours... [1](https://stackoverflow.com/q/75782146/2029983), [2](https://stackoverflow.com/q/75790206/2029983), [3](https://stackoverflow.com/q/75794352/2029983) – Thom A Mar 20 '23 at 19:21
  • Does this answer your question? [Operand data type varchar is invalid for divide operator](https://stackoverflow.com/questions/37155682/operand-data-type-varchar-is-invalid-for-divide-operator) – Thom A Mar 20 '23 at 19:34
  • 1
    [Bad Habits to Kick : ORDER BY ordinal](https://sqlblog.org/2009/10/06/bad-habits-to-kick-order-by-ordinal) – Thom A Mar 20 '23 at 19:36
  • 1
    Store numbers as numbers, and forget about this problem. – The Impaler Mar 20 '23 at 20:49

1 Answers1

2

Try the following instead since you cannot divide "string" values. Consider converting or casting to a decimal value. I'm assuming total_cases is nonzero in my answer.

select 
    location, 
    date, 
    total_cases, 
    total_deaths, 
    CONVERT(DECIMAL(18, 2), (CONVERT(DECIMAL(18, 2), total_deaths) / CONVERT(DECIMAL(18, 2), total_cases))) as [DeathsOverTotal]
from CovidDeaths#xlsx$
order by 1,2
Adam
  • 2,422
  • 18
  • 29