SUM only numbers in varchar column

Question

This topic has been reported for inappropriate content

SUM only numbers in varchar column

sqlmsbi

Valued Member

Points: 62
More actions
April 8, 2020 at 9:07 pm

#3741598
Below is the sample data
```
create table temp (userid int , rid varchar(10), value varchar(100))
insert into temp(userid,rid,value)
values(1,'D01','3'),
(1,'D01','4'),
(2,'C01','hey'), 
(2,'C01','1')
```
expected output:
1,'D01','7'
2,'C01','hey'
2,'C01','1'
I tried below code and it is throwing error
select distinct userid,rid,
case when ISNUMERIC(value) = 1
THEN
SUM(cast(value as int))
over (partition by userId,rid order by userid)
else value
end as [Value]
from temp

Viewing 4 posts - 1 through 3 (of 3 total)

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

BTylerWhite SSCommitted Points: 1569 More actions · Answer 1

To stay on the safe side and since you're on SQL Server 2012, you can use TRY_CAST rather than ISNUMERIC here. ISNUMERIC can do some strange things, as Phil Factor mentions in his article here.

Would you be able to separate these out using a UNION ALL?

SELECT userid, rid, CAST(SUM(CAST([value] AS int)) AS varchar (100)) AS [value]
FROM temp
WHERE TRY_CAST([value] AS int) IS NOT NULL
GROUP BY userid, rid
UNION ALL
SELECT userid, rid, [value]
FROM temp
WHERE TRY_CAST([value] AS int) IS NULL;

This would also avoid your conversion errors.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

SELECT t.userid
      ,t.rid
      ,Total = SUM(TRY_CAST(t.value AS INT))
FROM temp t
GROUP BY t.userid
        ,t.rid
ORDER BY t.userid;

But why on Earth do you have a numeric column defined as VARCHAR()???

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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

Phil Parkin wrote:

SELECT t.userid
      ,t.rid
      ,Total = SUM(TRY_CAST(t.value AS INT))
FROM temp t
GROUP BY t.userid
        ,t.rid
ORDER BY t.userid;

But why on Earth do you have a numeric column defined as VARCHAR()???

+100,000,000

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