Error converting data type nvarchar to numeric exception

Question

I tried to execute the query shown below in SQL Server Management Studio, but I get this error

Converting data type nvarchar to numeric exception

Query:

SELECT TOP 100 
    CASE WHEN ISNUMERIC(SUM(CAST([dbo].[Orders].[CustomerID] AS DECIMAL (38, 4)))) = 1 
            THEN CAST(SUM(CAST ([dbo].[Orders].[CustomerID] AS DECIMAL (38, 4))) AS INT) 
            ELSE NULL 
    END AS [Column1] 
FROM
    [dbo].[Orders]

Can anyone help me to clear this error?

Answer 1

Stop and Think. In general, summing an ID column makes no sense. Such a sum has no meaning unless there is something implicit in your schema. You could have 1000 orders for Customer 1 or 2 orders for Customer 500. Both situations sum to the same value - is that useful?

In addition, your error means that your ID column is not numeric. In this case, your use of isnumeric should be INSIDE the sum expression. The sum aggregate will ALWAYS return a numeric value; there is no useful reason to place it inside of an isnumeric/coalesce function.

And lastly, your schema is concerning. Someone chose to define the ID column as nvarchar - why? Perhaps there was a good reason but it seems unlikely at this point. That choice indicates that the table designer intended to allow any character in the ID column - which you now have confirmed. So I refer back to the first paragraph - at best you can only sum a subset of values from the column. Is that useful information?

Ultimately, if you need to do this then you reverse the relative positions of sum/isnumeric in your query.

select sum(case when isnumeric(CustomerID) = 1 
    then cast(CustomerID as ...) else 0 end) as Column1
from dbo.Orders;

And keep in mind that isnumeric is not as useful as one might expect. You can search the forums for alternatives. If you are a current version of sql server, you can use try_cast without the complexity of case/isnumeric.

Perhaps you meant to COUNT the orders for each customer?