By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,509 Members | 1,243 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,509 IT Pros & Developers. It's quick & easy.

Sum result of a Select

P: n/a
In the database i have a table with this information:

key_id =1
key_desc =43+34+22+12

I want sum the values in key_desc. Something like:

SELECT key_desc FROM table

But the result of the select was "111" and not "43+34+22+12".

Is this posible?

Jun 21 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On Jun 21, 12:44 pm, Apaxe <apaxe2000Milh...@gmail.comwrote:
In the database i have a table with this information:

key_id =1
key_desc =43+34+22+12

I want sum the values in key_desc. Something like:

SELECT key_desc FROM table

But the result of the select was "111" and not "43+34+22+12".

Is this posible?
Are you saying that the key_desc is a char() field with the value
"43+34+22+12" in it? If that's the case then this IS possible, but
the code is gonna make your head hurt a little bit :) Let me know and
I'll be happy to work with you on it.

Joshua

Jun 21 '07 #2

P: n/a
Apaxe (ap**************@gmail.com) writes:
In the database i have a table with this information:

key_id =1
key_desc =43+34+22+12

I want sum the values in key_desc. Something like:

SELECT key_desc FROM table

But the result of the select was "111" and not "43+34+22+12".

Is this posible?
I will have to confess that I understand about zero of your post. It would
certainly help if you could clarify what you are up to. I would suggest that
you post:

o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative that explains the business problem you are trying
to address.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 21 '07 #3

P: n/a
On Jun 21, 8:05 pm, "jlaust...@gmail.com" <jlaust...@gmail.comwrote:
On Jun 21, 12:44 pm,Apaxe<apaxe2000Milh...@gmail.comwrote:
In the database i have a table with this information:
key_id =1
key_desc =43+34+22+12
I want sum the values in key_desc. Something like:
SELECT key_desc FROM table
But the result of the select was "111" and not "43+34+22+12".
Is this posible?

Are you saying that the key_desc is a char() field with the value
"43+34+22+12" in it? If that's the case then this IS possible, but
the code is gonna make your head hurt a little bit :) Let me know and
I'll be happy to work with you on it.

Joshua
Hello, Joshua.

Yes, key_desc is a char() field. And i need to extract de sum of the
string. In this case, the value "111".

Thank you.

Jul 3 '07 #4

P: n/a
On Jul 2, 8:11 pm, Apaxe2000 <Apaxe2...@gmail.comwrote:
Yes, key_desc is a char() field. And i need to extract de sum of the
string. In this case, the value "111".
Simply create a temporary table (name it, for example "TheCalculator")
with only ONE record in it (Whatever...) and do something like:

SELECT (123+4)*2 FROM TheCalculator

That'll give you the result of any calculation

Piero

Jul 6 '07 #5

P: n/a

Apaxe :
In the database i have a table with this information:

key_id =1
key_desc =43+34+22+12

I want sum the values in key_desc. Something like:

SELECT key_desc FROM table

But the result of the select was "111" and not "43+34+22+12".

Is this posible?

1. Assuming you have a table and insertions like following ones
(using T-SQL syntax):
CREATE TABLE Test(key_id int, key_desc varchar(100))
INSERT INTO Test SELECT 1, '43+34+22+12' UNION SELECT 2,'34+56+78'

2. Assuming you have a following sequenve view (again using T-SQL)
CREATE VIEW [dbo].[Sequence](seq)
AS
SELECT hundred * 100 + ten * 10 + unit + 1
FROM (SELECT 0 UNION SELECT 1 UNION
SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION
SELECT 6 UNION SELECT 7 UNION
SELECT 8 UNION SELECT 9) AS Units(unit)
CROSS JOIN
(SELECT 0 UNION SELECT 1 UNION
SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION
SELECT 6 UNION SELECT 7 UNION
SELECT 8 UNION SELECT 9) AS Tens(ten)
CROSS JOIN
(SELECT 0 UNION SELECT 1 UNION
SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION
SELECT 6 UNION SELECT 7 UNION
SELECT 8 UNION SELECT 9) AS Hundreds(hundred)

3. Assuming you have only '+' operation, you can use a select like
following
SELECT Parsed.key_id,
SUM(Parsed.key_vls) sums
FROM (SELECT T.key_id,
CAST (SUBSTRING ('+' + T.key_desc + '+', MAX(S1.seq +
1), (S2.seq - MAX(S1.seq + 1))) AS INTEGER)
FROM Test AS T,
Sequence AS S1,
Sequence AS S2
WHERE SUBSTRING ('+' + T.key_desc + '+', S1.seq, 1) = '+'
AND SUBSTRING ('+' + T.key_desc + '+', S2.seq, 1) = '+'
AND S1.seq < S2.seq
AND S2.seq <= LEN(T.key_desc) + 2
GROUP BY T.key_id, T.key_desc, S2.seq
) AS Parsed(key_id, key_vls)
GROUP BY Parsed.key_id
If you have more operators in your string you have to add to this
query additional checks.
Idea is got from the book "JOE CELKO'S SQL PROGRAMMING STYLE".

Hope this helps,
Andriy

Jul 10 '07 #6

P: n/a
On Jun 21, 8:44 pm, Apaxe <apaxe2000Milh...@gmail.comwrote:
In the database i have a table with this information:

key_id =1
key_desc =43+34+22+12

I want sum the values in key_desc. Something like:

SELECT key_desc FROM table

But the result of the select was "111" and not "43+34+22+12".

Is this posible?
This looks similar to one of your other problems see
http://groups.google.com/group/micro...4b73956cc1b7f8

How come you did not clean the data before it got into the database?

Assuming max 4 values (columns) to sum

CREATE TABLE Test(key_id int, key_desc varchar(100))
INSERT INTO Test SELECT 1, '43+34+22+12' UNION SELECT 2,'34+56+78'

select
convert(int,isnull(PARSENAME(replace(key_desc,'+', '.'),4),0))
+convert(int,isnull(PARSENAME(replace(key_desc,'+' ,'.'),3),0))
+convert(int,isnull(PARSENAME(replace(key_desc,'+' ,'.'),2),0))
+convert(int,isnull(PARSENAME(replace(key_desc,'+' ,'.'),1),0))
from Test

or else you can do:

declare c1 cursor for select convert(varchar(9),key_id),key_desc from
Test
open c1
declare @id int, @idc varchar(9), @key_desc varchar(100)
select key_id, 0 'total' into #t1 from Test where 1=2
fetch c1 into @idc, @key_desc
while @@FETCH_STATUS = 0
begin
exec ( 'insert into #t1 select key_id, '+@key_desc+' from Test where
key_id='+@idc)
fetch c1 into @idc, @key_desc
end
close c1
select * from #t1
drop table #t1
deallocate c1
drop TABLE Test

Jul 10 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.