Prev: Connection string to SQL Server to 2005
Next: tsql to change oracle pswd
From: RedGlow06 on 22 Dec 2009 14:06
I have a field that contains values like "1,3,0,0" and "2,0,0" and "1". I
need to sum those values within the field. The field can have up to 100
numbers seperated by commas. How can I do sum them?

so in example 1 "1,3,0,0" I would want it to say 4
example 2 "2,0,0" I would want it to say 2
example 3 "1" I would want it to say 1

Any help is much appreciated. Thanks!

From: Bob Barrows on 22 Dec 2009 14:43
RedGlow06 wrote:
> I have a field that contains values like "1,3,0,0" and "2,0,0" and
> "1". I need to sum those values within the field. The field can
> have up to 100 numbers seperated by commas. How can I do sum them?
>
> so in example 1 "1,3,0,0" I would want it to say 4
> example 2 "2,0,0" I would want it to say 2
> example 3 "1" I would want it to say 1
>
> Any help is much appreciated. Thanks!

Given the creation of a table called Numbers:
********************************************************************
CREATE TABLE dbo.Numbers
(
Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
)

WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 1024
BEGIN
INSERT dbo.Numbers DEFAULT VALUES
END
********************************************************************

This will do what you want:
select 1 as ID,'1,3,0,0' as vals into #tmp
union select 2,'2,0,0'
union select 3,'1'

select id, vals,sum(number) as total
from (
select id,vals,Number
from #tmp as t , numbers as n
WHERE CHARINDEX
(
','+CONVERT(VARCHAR(12),Number)+',',
','+vals+','
) > 0
) as q
group by id,vals


Now you can read the rest of the answers which will undoubtedly start by
castigating the designer of this database for storing multiple pieces of
data in a single column, thus violating normal form and causing the
problem you are now experiencing.

--
HTH,
Bob Barrows


From: RedGlow06 on 22 Dec 2009 15:38
It looks to be working ok except when I have 2 of the same values like "1,1,
0" shows up as 1 or "1,2,1,0" shows up as 3. Any suggestions?

Bob Barrows wrote:
>> I have a field that contains values like "1,3,0,0" and "2,0,0" and
>> "1". I need to sum those values within the field. The field can
>[quoted text clipped - 5 lines]
>>
>> Any help is much appreciated. Thanks!
>
>Given the creation of a table called Numbers:
>********************************************************************
>CREATE TABLE dbo.Numbers
>(
> Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
>)
>
>WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 1024
>BEGIN
> INSERT dbo.Numbers DEFAULT VALUES
>END
>********************************************************************
>
>This will do what you want:
>select 1 as ID,'1,3,0,0' as vals into #tmp
>union select 2,'2,0,0'
>union select 3,'1'
>
>select id, vals,sum(number) as total
>from (
> select id,vals,Number
> from #tmp as t , numbers as n
> WHERE CHARINDEX
> (
> ','+CONVERT(VARCHAR(12),Number)+',',
> ','+vals+','
> ) > 0
>) as q
>group by id,vals
>
>Now you can read the rest of the answers which will undoubtedly start by
>castigating the designer of this database for storing multiple pieces of
>data in a single column, thus violating normal form and causing the
>problem you are now experiencing.
>

From: Bob Barrows on 22 Dec 2009 16:00
1. Shoot the database designer ;-)
2. Discard using the Numbers table approach. You will need a function
for this. Unfortunately, I don't have time to write one right now. If no
one posts an answer, you can find one yourself by googling

SQL parse comma delimited string


RedGlow06 wrote:
> It looks to be working ok except when I have 2 of the same values
> like "1,1, 0" shows up as 1 or "1,2,1,0" shows up as 3. Any
> suggestions?
>
> Bob Barrows wrote:
>>> I have a field that contains values like "1,3,0,0" and "2,0,0" and
>>> "1". I need to sum those values within the field. The field can
>> [quoted text clipped - 5 lines]
>>>
>>> Any help is much appreciated. Thanks!
>>
>> Given the creation of a table called Numbers:
>> ********************************************************************
>> CREATE TABLE dbo.Numbers
>> (
>> Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
>> )
>>
>> WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 1024
>> BEGIN
>> INSERT dbo.Numbers DEFAULT VALUES
>> END
>> ********************************************************************
>>
>> This will do what you want:
>> select 1 as ID,'1,3,0,0' as vals into #tmp
>> union select 2,'2,0,0'
>> union select 3,'1'
>>
>> select id, vals,sum(number) as total
>> from (
>> select id,vals,Number
>> from #tmp as t , numbers as n
>> WHERE CHARINDEX
>> (
>> ','+CONVERT(VARCHAR(12),Number)+',',
>> ','+vals+','
>> ) > 0
>> ) as q
>> group by id,vals
>>
>> Now you can read the rest of the answers which will undoubtedly
>> start by castigating the designer of this database for storing
>> multiple pieces of data in a single column, thus violating normal
>> form and causing the problem you are now experiencing.

--
HTH,
Bob Barrows


From: Bob Barrows on 22 Dec 2009 17:15
OK, here's one way to skin this cat:

create function dbo.SumDelimited (
@vals varchar(2000)
)
RETURNS int
AS
BEGIN
declare @retval int, @val int
declare @pos int

set @vals=(a)vals + ','
set @retval=0
while patindex('%,%',@vals) <> 0
begin
set @pos=patindex('%,%',@vals)
set @val=left(@vals,@pos-1)
set @retval=(a)retval + @val
set @vals=stuff(@vals,1,@pos,'')
end

RETURN @retval
END

select 1 as ID,'1,3,1,0' as vals into #tmp
union select 2,'2,20,0'
union select 3,'1'

select id,vals,dbo.SumDelimited(vals) Total
from #tmp

Of course, you will need to add some validation and error-handling code
in there just in case someone screws up and enters invalid data (which
is another one of the pitfalls of using a non-normalized database design
.... *)


*If you're not getting the impression that I disapprove of this table's
design, you're not reading closely enough :-)

RedGlow06 wrote:
> It looks to be working ok except when I have 2 of the same values
> like "1,1, 0" shows up as 1 or "1,2,1,0" shows up as 3. Any
> suggestions?
>
> Bob Barrows wrote:
>>> I have a field that contains values like "1,3,0,0" and "2,0,0" and
>>> "1". I need to sum those values within the field. The field can
>> [quoted text clipped - 5 lines]
>>>
>>> Any help is much appreciated. Thanks!
>>
>> Given the creation of a table called Numbers:
>> ********************************************************************
>> CREATE TABLE dbo.Numbers
>> (
>> Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
>> )
>>
>> WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 1024
>> BEGIN
>> INSERT dbo.Numbers DEFAULT VALUES
>> END
>> ********************************************************************
>>
>> This will do what you want:
>> select 1 as ID,'1,3,0,0' as vals into #tmp
>> union select 2,'2,0,0'
>> union select 3,'1'
>>
>> select id, vals,sum(number) as total
>> from (
>> select id,vals,Number
>> from #tmp as t , numbers as n
>> WHERE CHARINDEX
>> (
>> ','+CONVERT(VARCHAR(12),Number)+',',
>> ','+vals+','
>> ) > 0
>> ) as q
>> group by id,vals
>>
>> Now you can read the rest of the answers which will undoubtedly
>> start by castigating the designer of this database for storing
>> multiple pieces of data in a single column, thus violating normal
>> form and causing the problem you are now experiencing.

--
HTH,
Bob Barrows


 |  Next  |  Last
Pages: 1 2 3
Prev: Connection string to SQL Server to 2005
Next: tsql to change oracle pswd