Operand data type nvarchar is invalid for subtract operator.
Issue- In one of the SQL Groups forum one of the user asked a question
that we want the diff of Max value of one column and min value of other column
Difference=Max(Column1)-Min(Column2)
Below is the user Original question
Question-i
am facing a small problem .kindly help me
select
min(low) as low, max(high) as high from <table_name>
now i want to find the difference between two results
i used
select (select max(high)from <table_name>)-(select min(low)from <table_name>) as diff
This is not working ... kindly help me
now i want to find the difference between two results
i used
select (select max(high)from <table_name>)-(select min(low)from <table_name>) as diff
This is not working ... kindly help me
Table
screenshot
Solution-So
for this I created a test script with same table structure so below is the
script with output
create table x1
(name nvarchar(100),
high float,
low float
)
insert into x1 values ('xyz', 50.5, 48),('xyz', 60.2 ,50.3),('xyz', 55, 45),('xyz' ,62.9 ,47.6)
select max(high)MaxHigh,min(low)MinLow ,max(high)-min(low) differences from x
MaxHigh MinLow differences
62.9 45
17.9
After
giving the Query to him he informed me that he is getting below error
Msg 8117,
Level 16, State 1, Line 1
Operand
data type nvarchar is invalid for subtract operator.
In case if you are applying multiplication then you will get the below error.
Msg 8117, Level 16, State 1, Line 1
Operand data type nvarchar is invalid for multiply operator
In case if you are applying multiplication then you will get the below error.
Msg 8117, Level 16, State 1, Line 1
Operand data type nvarchar is invalid for multiply operator
In case if you are applying divide then you will get the below error.
Msg 8117, Level 16, State 1, Line 1
Operand data type nvarchar is invalid for divide operator.
Msg 8117, Level 16, State 1, Line 1
Operand data type nvarchar is invalid for divide operator.
These Errors means columns are not int or float type they
are varchar type so i casted them into float to give him result without any error.
Solution with casting the varchar field to float.
Solution with casting the varchar field to float.
select max( cast(high as float))-min(cast(low as float)) diff from x
Output
diff
17.9
or as per his query
select (select max( cast(high as float))from x)-(select min(cast(low as float)) from x) as diff
Output
diff
17.9
NOTE: Always keep in mind that applying any arithmetic operator or function in any
varchar field you need to first cast or convert it into int, float or decimal.
No comments:
Post a Comment