question

Hi, I don't think you need the outer convert. In the CASE the convert needs to be to a numeric datatype. Example below. Cheers Matt CREATE TABLE #Test ( Id INT IDENTITY(1, 1), quantity_ordered VARCHAR(50) ); INSERT INTO #Test VALUES ('abc'), ('126.00'); SELECT CASE WHEN ISNUMERIC(CONVERT(VARCHAR(12), a.quantity_ordered)) = 1 THEN CONVERT(DECIMAL(18, 9), a.quantity_ordered) ELSE 0 END FROM #Test a;

From the question definition the version of SQL Server is not clear, but if it is 2012 or higher then it would be a very good idea to avoid using ISNUMERIC. This function merely determines whether there is some way to convert the value to one of the available numeric data types. This does not mean, however, that the conversion will succeed even though the ISNUMERIC returned 1. For example run select isnumeric('$'), isnumeric('2e3'), isnumeric('.') ISNUMERIC returns 1 for all of them, but try to cast/convert any of these to decimal or integer. If the SQL Server in question is 2012 or higher, it is easier to use the [try_cast][1] function. This will allow restating the select statement in question like this: select isnull(try_cast(a.quantity_ordered as decimal(12, 3)), 0) quantity_ordered from dbo.xx a; The resulting expression will return the numeric value (converted to decimal) for any values that can be converted, otherwise NULL. Using isnull around it allows the resulting values to be either converted numbers or the zeroes for those values which cannot be converted. As @mjharper already pointed out in the comments, the **quantity\_ordered** column is ***not*** to be included in the group by clause. The ISNULL is not needed either because the SUM disregards the null values anyway, so with try\_cast, the query may look like this: select a.item_key, sum(try_cast(a.quantity_ordered as decimal(12, 3))) quantity_ordered from dbo.xx a group by a.item_key; Hope this helps. Oleg [1]: https://docs.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql