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?
On Jun 21, 12:44 pm, 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?
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
Apaxe (apaxe2000Milhoes@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
On Jun 21, 8:05 pm, "jlaust...@gmail.com"
> On Jun 21, 12:44 pm,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?
>
> 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.
On Jul 2, 8:11 pm, Apaxe2000
> 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
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
On Jun 21, 8:44 pm, 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?
This looks similar to one of your other problems see
http://groups.google.com/group/microsoft.public.excel/browse _thread/thread/4964397f4e248e1d/0f4b73956cc1b7f8#0f4b73956cc 1b7f8
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