June 21, 2011 at 10:11 am
consider number 219.
If I want to get the sum of the numbers How do I write the query (2+1+9=12)
In the result I need to get 12 as the answer.
Thanks in advance.
June 21, 2011 at 10:33 am
Well that's interesting. I think a small tally table, and substring splitting as shown here might be the way to do this: http://www.sqlservercentral.com/articles/Tally+Table/70738/
I'd adapt that you pull out each number as a string, then cast to int and sum them.
June 21, 2011 at 10:33 am
Out of curiosity, why do you need to do this?
June 21, 2011 at 10:35 am
Steve Jones - SSC Editor (6/21/2011)
Out of curiosity, why do you need to do this?
Its dam easy when u do that in Java or C. But I dont even get the syntax right in SQL. So just like that.having fun thats it.
Thanks for the info provided
June 21, 2011 at 10:59 am
could be done mathematically too:
Declare @number int, @acc int
select @number = 219, @acc = 0
while 1=1
Begin
set @acc = @acc + @number % 10 ; set @number = @number / 10
if @number = 0
break
End
select @acc
June 21, 2011 at 11:00 am
thank u
June 21, 2011 at 11:01 am
Jeremy-475548 (6/21/2011)
could be done mathematically too:
Declare @number int, @acc int
select @number = 219, @acc = 0
while 1=1
Begin
set @acc = @acc + @number % 10 ; set @number = @number / 10
if @number = 0
break
End
select @acc
Say if I have a table complete of these numeric values and I want the sum of each of those numbers how do you think of doing it and thanks for this
June 21, 2011 at 11:09 am
OK...don't skewer me for this 🙂
create function SumDigits(@value int) returns int
as
begin
Declare @acc int
set @acc =0
while 1=1
Begin
set @acc = @acc + @value % 10 ; set @value = @value / 10
if @value = 0
break
End
return @acc
end
create table #tmp(MyNumber int)
insert into #tmp
select 200
union all select 234
union all select 555
union all select 666
union all select 777
Select dbo.SumDigits(MyNumber) From #tmp
drop table #tmp
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply