Converting to int and aggregating at the same time

Question

Post reply

Converting to int and aggregating at the same time

Mark Green

SSCrazy

Points: 2085
More actions
October 10, 2006 at 4:59 am

#113871

I have a T-SQL statement that includes:
SELECT P.Col001, P.Col002, P.Col003, A.Age_start_INT, A.Age_end_INT, SUM(P.Col006)
But this is not allowed because P.Col006 is a string. Is it possible for me to Convert the text values to an int and do a SUM operation at the same time?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Vladan SSC-Insane Points: 22072 More actions · Answer 1

Yes. You can use both SUM(CAST(P.Col006 AS INT)) or SUM(CONVERT (INT, P.Col006)).

You can find this in the help files of SQL Server (BOL - Books OnLine) under "CAST and CONVERT".

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

a little better....

SUM((CASE WHEN ISNUMERIC(P.Col006) = 1 THEN CAST(P.Col006 AS INT) ELSE 0 END))

--Ramesh

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

ISNUMERIC does NOT check for numeric characters only... be careful.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vladan SSC-Insane Points: 22072 More actions · Answer 4

True, I didn't mention error checking... thanks for bringing it up, Ramesh.

Problem is that ISNUMERIC() does not always return what one would suppose. Be careful, even with this check some incorrect values may still pass the test - because they are considered numbers in some special form (like exponential):

SELECT ISNUMERIC('+002') --> 1

SELECT ISNUMERIC('-3E265') --> 1

SELECT ISNUMERIC('6D78') --> 1

SELECT ISNUMERIC('6D7897') --> 0

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 5

Vladan,

I does know that there are known issues with ISNUMERIC fn & i forgot to mention it..

Since 'D' & 'E' are valid formats for representing floating point numbers in Fortran & the internal routines used by SQL Server for ISNUMERIC uses the C functions that recognizes these formats. That doesn't necessarily mean that they can all be converted in T-SQL. MS is aware of these issues with this function & this has been reported to them several years back.

If you need better checks, then use the PATINDEX function to find matches. And also remember that ISNUMERIC returns TRUE for any conversion from string to any of the numeric data types not just int.

--Ramesh

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

You can use the PatIndex if you want... I prefer the regular expressions because they seem to be simpler and they run with good speed...

For example, if you had a VARCHAR column called Col006 (from some of the previous examples that contained a mix of things that were summable (all digits) and things that were not summable and Col001 was the column to GORUP BY, the following would work nicely...

SELECT Col001,

Col006Sum = SUM(CASE WHEN Col006 NOT LIKE '%[^0-9]%'

THEN CAST(Col006 AS INT)

ELSE 0

END)

FROM yourtable

GROUP BY Col001

The "^" is the regular expression for NOT. Yeah, I know... two NOTS... trust me, it's still wicked fast and is guaranteed to correctly discriminate between ALL DIGITS or not where ISNUMERIC would allow errors to creep in.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)