I have fields with decimal values but imported in database as nvarchar(50) data type, that look like this:

Goal: I wanted to aggregate (SUM) these fields to get the sum of orders and total number of units ordered

I tried to cast these values as numeric(10,2) because I wanted to aggregate these fields (SUM):

SELECT 
    [Time],
    SUM(CAST([Ordered_product_sales] AS NUMERIC(10, 2))) AS Sales_per_day,
    SUM(CAST([Units_ordered] AS INT)) AS num_of_units_ordered
FROM 
    [dbo].[salesDashboard]
WHERE      
    Ordered_product_sales <> '0.00' 
    AND Ordered_product_sales IS NOT NULL
GROUP BY 
    Time

However, I am getting a following error:

Error converting data type nvarchar to numeric.

CodePudding user response：

The issue causing your error is that the data strings in the [Ordered_product_sales] column contain characters that cannot be converted to a numeric type. You must first sanitize the strings to remove all characters that cannot be converted, specifically the '$' present in your sample data, prior to performing the CAST.

This should work:

SELECT SELECT 
[Time],
SUM(CAST(REPLACE([Ordered_product_sales], '$', '') AS NUMERIC(10, 2))) AS Sales_per_day,
SUM(CAST([Units_ordered] AS INT)) AS num_of_units_ordered

And you should put some thought and effort to improving the design of your table and columns with respect to datatypes.