how to sum a text field with commas

[RedGlow06]

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!

[Bob Barrows]

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

[RedGlow06]

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]

[Bob Barrows]

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

--
HTH,
Bob Barrows

[Bob Barrows]

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=@vals + ','
set @retval=0
while patindex('%,%',@vals) <> 0
begin
set @pos=patindex('%,%',@vals)
set @val=left(@vals,@pos-1)
set @retval=@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 :-)

--
HTH,
Bob Barrows

[Erland Sommarskog]

Look here for a number of methods:
http://www.sommarskog.se/arrays-in-sql.html

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[TheSQLGuru]

There is a massive forum thread on sqlservercentral on string parsing,
complete with countless solutions to the problem and a dizzying array of
benchmarks.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

"RedGlow06" <u57010@uwe> wrote in message news:a0fc0f89c2b74@uwe...

[--CELKO--]

>> I have a field [sic: columns are not fields] that contains values like "1,3,0,0" and "2,0,0" and "1".  I need to sum those values within the field [sic].  The field [sic] can have up to 100 numbers separated by commas.  How can I do sum them? <<

BY DEFINITION a column has scalar values, this is not a valid value in
SQL. This violates a thing called First Normal Form (1NF) which you
will find in the first few chapters of a book on RDBMS.

You can find lots of stinking kludges to parse strings, but the right
answer is to design a proper schema and fire the guy who did this.

If you use a kludge, be sure to include code to raise all the errors
that a parameter in a procedure call can raise. The cowboy coders
never bother with that part ..

[Tony Rogerson]

> BY DEFINITION a column has scalar values, this is not a valid value in
> SQL. This violates a thing called First Normal Form (1NF) which you

I think you are confusing the query language (SQL, or in this product TSQL)
with database design.

In database design yes, you would eradicate this type of structure.

SQL is querying - nothing more.

You also show a sheltered existence, really, have you never had to chop and
refine data - would you honestly write a separate one off program in C when
in a fraction of the time you could cut and massage the data into a
structure you want and conforms to proper database design principles?

--ROGGIE--

"--CELKO--" <jcel...@earthlink.net> wrote in message
news:6ab35ce4-1acc-48cb...@c34g2000yqn.googlegroups.com...

[--CELKO--]

>> SQL is querying - nothing more. <<

Actually it is DDL, DML, DCL and transaction control. This is one of
the major problems that cowboy coders have; they think SQL = DML, and
fail to create integrated systems.

>> You also show a sheltered existence, really, have you never had to chop and refine data - would you honestly write a separate one off program in C when in a fraction of the time you could cut and massage the data into a structure you want and conforms to proper database design principles? <<

LOL! These days, I use a word processor or a spreadsheet most of the
time! I find that most one-shot jobs have the data I want on a
website for download. Most of the cleanup work is with text --
squeeze spaces, change case, run a macro and stick it into a CSV file
for BCP or some INSERT INTO statements.

For repeated jobs, we usually have an ETL tool in my "sheltered
existence" IT shops.

[Tony Rogerson]

> Actually it is DDL, DML, DCL and transaction control. This is one of
> the major problems that cowboy coders have; they think SQL = DML, and
> fail to create integrated systems.
>

You said this...

"BY DEFINITION a column has scalar values, this is not a valid value in SQL"

Holding "1,2,3" in a column has nothing to do with DDL, DML, DCL, {querying}
and transaction control.

IT has everything to do with database design 1nf, 2nf etc...

> LOL! These days, I use a word processor or a spreadsheet most of the
> time! I find that most one-shot jobs

Next time (later this morning) I need to bring in a 20MByte file with a
couple of million rows in it and validate it, and then insert it into my
database I'll be sure to try and do that with Word or Excel.

Right tool for the right job; often SSIS is over kill and takes longer for
what you are trying to do; a lot of the time its easier, more maintainable,
more cost effective to load the data into temporary tables within the
database and use "T-SQL" to validate etc...

But, you'd not know that if you worked just with theory; its only by
actually doing stuff you'd get to know this "experience".

> For repeated jobs, we usually have an ETL tool in my "sheltered
> existence" IT shops.
>

Cool - so, a lot of experience on a day to day basis doing that? Or is it,
as I suspect; something you've only occasionally done
.
Perhaps one of these days I'll bring a lap top with some of the products you
say you have used and put it in front of you and say "go on then, should me
the basics of the product" - I've no doubt in my mind you'd be clueless.

--ROGGIE--

"--CELKO--" <jcel...@earthlink.net> wrote in message

news:01905906-cbad-451f...@j24g2000yqa.googlegroups.com...