select convert(int, Price_Each) * Quantity_Ordered as Total_Sale
from data
I am trying to multiply two columns where data type of Price_Each is varchar(50), and data type of Quantity_Ordered is int.
Even after convert and casting I am having the same error:
Conversion failed when converting the varchar value ' $11.95' to data type int.
My table name is "DATA"
| Order ID | Product | Quantity Ordered | Price Each |
|---|---|---|---|
| 176558 | USB-C Charging Cable | 2 | $11.95 |
| 176559 | Bose SoundSport Headphones | 1 | $99.99 |
My problem statement is: create new columns named as total sales per person (by multiplying quantity order with the price each)
Could anyone please help me out?
CodePudding user response：
As mentioned in comment by @Jeroen Mostert, you need to convert to money which handles the currency symbol $. The query would be :
select convert(money, Price_Each) * Quantity_Ordered as Total_Sale
from data
And you should actually change your column datatype to money, then you can easily multiply without casting and definitely you will get more benefits while doing any other processing with this column.
alter table [DATA]
alter column Price_Each money