SQL- How to SUM varchar with text in some fields

Try this

select sum(cast(lookup2 as float)) from YourTable

Raj

When I do this I get the following error:
Error in compliling SQL expression: DB connector error 42000 ODBC SQL server. Error converting data type varchar to float. DB vendor code 8114

select sum(cast(ltrim(rtrim(lookup2)) as float)) from YourTable

I think, some of your may not be integer. anyway if there is any space causing this error, this may fix.

Raj

select sum(cast(lookup2 as float)) from YourTable
where isnumeric(lookup2) = 1

same error as before. one example of the old text is:  03)1 hour.  Now it is just 1 for the one hour.  I am using this command in Crystal version 11.  I am by far not a report builder in Crystal, but I can ususally get out what I need if it is simple.

maybe I am typing this wrong. If I am in the expression builder, and my table location is "tasks.lookup2" what is the "exact" line(s) that I would type?
Thanks!

I guess I am just missing something, I tried the script to find the text from the lookup2 column and I ran it from the SQL 2005 studio manager. If I run it like you have it, it says "invaild column" If I put in the entire DB path "dbo.tasks.lookup2" is says the multi-part identifier could not be bound. Should it be this complicated?

post the full query, you tried.

Raj

The problem with using Val (), as mlmcc suggested, is that it takes the characters up to the first non-numeric character and converts those to a number.  Using your example of "03)1 hour", it would take the "03" and convert that to 3, instead of pulling the 1 in "1 hour".

 If you can define which characters in these strings are the numbers that you want to convert, you can probably do this (either in SQL or in CR).  If you can't come up with some consistent rules, then you won't be able to get reliable results.

 Using your example of "03)1 hour", you could say that you should discard everything through the ")" and the numeric characters right after that (in this case, "1") are your number.  But do the other text values follow the same format?

 James

Thought of this just after I posted.

 For the record, even if Val would work, IsNumeric (as in mlmcc's formula) won't work, because it looks for fully numeric strings.  It will be false on strings like "03)1 hour".  So, you'd just use Val ({field}), without the IsNumeric test.  But, like I said, I don't think Val will give you what you want anyway.

 James

My attempt assumed you wanted only numeric fields added.  However if there is data like 03) 1 hour and you need 1 added then you will need to determine what data needs to be editted and try to account for each case.

mlmcc

ok so it sounds like it would be easier if the table in SQL all contained only numeric values. How about this. is there a way from SQL to do a find and replace like; find all 02) 30 min (and) change to .50 and so on?  If so, I will just update the tables. I need the data in the tables but it can change to the new format.
Thanks.

It's possible, if you can come up with specific patterns to look for.  That may be the tricky part.

 James

I have converted all fields to a value of .25 .50 1 2 3 4 5 6 7 8 and so on. Now I have tried both the statements given by Raj and aneeshattingal: but in both case in Crystal Report formula editor I get the following message; "the ) is missing" and it always highlights the word "cast"  Any ideas? It does seem to be getting closer.
Thanks
JC

OK I can successfully put in the Val or ToNumber statement and not get an error, but I dont get the correct count. Here is a full example, sorry I am making this so hard but I am a hardware person and not a SQL/Crystal guy!
My table is "Tasks" my column is "Lookup2" Lookup2 is a varchar field.
My currunt values are 0,0,.25,.25,.25,0,0,3,0,2,2,1,2,0,1,1,0,.25,0 for my lookup2 column for user1 in the report.  My statement is
if not IsNull ({Tasks.lookup2}) then
Val ({Tasks.lookup2})
My answer is always the LAST value in the column and not the sum of the numbers. I have tried with the statement mlmmc suggested and with ToNumber statement.
My answer should be 13.  What could I be doing wrong?
I created this statement in Crystal Reports x+1 field.

Thanks so much!
JC

Where did you create it?

Where is it in the report?

mlmcc

Since you accepted a solution, I guess you've figured this out, but it sounded like you just needed to do a summary on that formula.

 James

yes after I put in the statement I still did not have it in the correct "group" to sum up the fields. I don't work with Crystal or SQL in dept at all so i was stumbling my way through. With all the help everyone gave me I was able to get the results I wanted. Thanks so much.
JC

OK.  Glad you were able to figure out that last problem.

 James