CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
neophyte
neophyte

Reputation: 1816

SQL Server Sum rows with string value

I have a dynamic SQL query which returns rows like below with string values & numeric values.

EMP col1 col2 col3 col4 col5
----------------------------
A1   4     4    3   3   3
A2   4     2    5   3   3
A3  sd     3    3   1   sd
A4   3     4    3   3   3

Now I need a new column which sums col1 to col5 and creates a total sum column where it should ignore the string values as in row 3. There are no NULL values

How could I achieve this? Using ISNUMERIC might be the solution, but I'm not sure how to use it in such a scenario.

Upvotes: 1

Views: 6071

Answers (4)

Bryan
Bryan

Reputation: 17703

If you're on SQL Server 2012, TRY_CONVERT avoids pitfalls commonly encountered with ISNUMERIC:

SELECT col1, col2, col3, col4, col5,
       ISNULL(TRY_CONVERT(int, col1), 0) +
       ISNULL(TRY_CONVERT(int, col2), 0) +
       ISNULL(TRY_CONVERT(int, col3), 0) +
       ISNULL(TRY_CONVERT(int, col4), 0) +
       ISNULL(TRY_CONVERT(int, col5), 0) AS total
FROM Employee

SQLFiddle

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can do this with a big case statement:

select q.*,
       ((case when isnumeric(col1) = 1 then cast(col1 as int) else 0 end) +
        (case when isnumeric(col2) = 1 then cast(col2 as int) else 0 end) +
        (case when isnumeric(col3) = 1 then cast(col3 as int) else 0 end) +
        (case when isnumeric(col4) = 1 then cast(col4 as int) else 0 end) +
        (case when isnumeric(col5) = 1 then cast(col5 as int) else 0 end)
       ) as newcol  
from q;

isnumeric() should be sufficient for your purposes. You might need fancier logic if you only want positive integers or want to exclude exponential notations or the like.

Upvotes: 1

Andrew
Andrew

Reputation: 8703

You're on the right track with isnumeric:

select
emp,
(case when isnumeric(col1) = 1 then cast(col1 as int) else 0 end) +
col2...
from table1

Upvotes: 0

Adam Porad
Adam Porad

Reputation: 14471

You can use a CASE Expression to determine whether the value is a number. If it is a number then either cast the value to an INT or DECIMAL data type, otherwise use 0 so it doesn't effect the sum.

SELECT
     CASE WHEN ISNUMERIC(col1) = 1 THEN CAST(col1 as INT) ELSE 0 END
   + CASE WHEN ISNUMERIC(col2) = 1 THEN CAST(col2 as INT) ELSE 0 END
   + CASE WHEN ISNUMERIC(col3) = 1 THEN CAST(col3 as INT) ELSE 0 END
   + CASE WHEN ISNUMERIC(col4) = 1 THEN CAST(col4 as INT) ELSE 0 END
   + CASE WHEN ISNUMERIC(col5) = 1 THEN CAST(col5 as INT) ELSE 0 END as SumValue
FROM MyTable

Upvotes: 3

Related Questions