SQL Server : SUM () for only numeric values, ignoring Varchar values

Question

I need some help as I am learning SQL so I am at a beginner level. I am using SQL Server 2008 R2.

My question is: I want to (add) sum up a column that have mix values of varchar and decimal. I want to ignore the varchar values and sum only decimal. My column is like this:

Column1  
-------
0.1  
Z  
0.4  
2.1  
2.1  
Z

And I need the sum that is in this case it should be: 4.7

I tried using CASE but I failed to solve it

SELECT 
    SUM(CASE 
           WHEN ISNUMERIC(Column1) = 1 
              THEN .(if true how i add them?). 
              ELSE .(and if false how i ignore them?). 
         END) AS Total 
FROM
    TABLE_Name

I am not good in explaning things, but I hope you can understand me what I am trying to do.

If this question is already answered please give me directions to that question and my appologise for that.. Many thanks in advance :)

Answer 1

Simply use WHERE clause with CAST:

SELECT SUM(Cast(Column1 as numeric(10,2))) as Total
FROM TABLE_Name
Where IsNumeric(Column1) = 1 

Result:

TOTAL
4.7

Sample result in SQL Fiddle.

EDIT:

As @funkwurm pointed out, the column has a chance to have the value '.' (dot):

SELECT SUM(Cast(Column1 as numeric(10,2))) as Total
FROM TABLE_Name
Where IsNumeric(Column1 + 'e0') = 1 

Fiddle.