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 :)

The answer is to store data in the right place, to never have the problem to sum a non-numeric column! — jarlh, Mar 02 '15 at 08:15
This is a **clear sign** of a **horribly bad** database design - you should **fix that FIRST!** before wasting more time on trying to sum up such a messy column .... — marc_s, Mar 02 '15 at 08:45

Raging Bull · Accepted Answer · 2015-03-02T08:16:01.567

11

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.

edited Mar 02 '15 at 08:16

answered Mar 02 '15 at 07:36

Raging Bull

18,593
13
50
55

2

Thank you very much. Really Quick answer :) – Bilawal Mar 02 '15 at 07:49
3

As per [this answer](http://stackoverflow.com/a/4522185/2684660) I would recommend doing `IsNumeric(Column1 + 'e0') = 1`. If I change the SqlFiddle to have a row with value `.` for `Column1`, it breaks. – asontu Mar 02 '15 at 08:01
@funkwurm: Thanks. I respect your opinion. Updated my answer. – Raging Bull Mar 02 '15 at 08:04

score 0 · Answer 2 · answered Mar 02 '15 at 07:37

You can "ignore" a row by adding 0.

SELECT
    SUM(
        CASE
            WHEN ISNUMERIC(Column1)=1 THEN CONVERT(DECIMAL(10,5), Column1) --add the value to the sum
            ELSE 0 --add 0 (i.e. ignore it)
        END)
    AS Total
FROM
    TABLE_Name

score 0 · Answer 3 · answered Mar 02 '15 at 07:41

0

try

select sum(case when isnumeric(cast(Column1 as decimal)) 
then cast(column1 as decimal) else 0 end) as total from table

assuming your field is varchar type

answered Mar 02 '15 at 07:41

Jim Chen

36
1
4

CeOnSql · Answer 4 · 2015-03-02T09:44:07.320

Why not using TRY_CONVERT:

SELECT SUM(TRY_CONVERT(float,Column1) AS Total
FROM TABLE_Name

Shortest query and easy to read! TRY_CONVERT returns NULL if conversion fails (e.g. if there are text values in Column1)

This Functions exists since SQL Server 2012 (i think).

@Jim Chen:

Isnumeric is a bit tricky a this point! In your solution there would be a problem if the value '-' or '+' is inside a Column:

SELECT ISNUMERIC('+')    --returns TRUE!
SELECT ISNUMERIC('-')    --returns TRUE!

==> SUM will FAIL!

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

4 Answers4