Calculating "Sum" of Numeric values in Varchar fld

itHighway · December 28th, 2004, 06:49 PM

Hello!

I have a Varchar field, in which has only numeric values. I want to calculate the sum of using the sql query.
Right now the query I"m using is:
SSQL = "select SUM(AMOUNT) AS VARAMOUNT from ORDER"

The above sql statement gives the following error:
Cannot calculate SUM of varchar field

Any idea..
Any help...
Will be appriciated....

Thanks
Zeeshan Ahmed

rajanikrishna · December 28th, 2004, 09:13 PM

Hi,

The varchar field in the sence it contains only numbers or alphanumeric characters? If numbers only, convert to any of numeric datatype.

------------
Rajani

mat41 · December 29th, 2004, 12:52 AM

itHighway
Your error explains it clearly:
;;;Cannot calculate SUM of varchar field
means what it says. Does this field need to be a varchar? varchars are stored as strings. Two options come to mind:
1..re think you data base design changing this field to some type of integer
2..Get the values, convert to integer...

Wind is your friend
Matt

aditya_buxar · December 29th, 2004, 05:16 AM

hi ithieghway

If your field contain only number then try this code

select sum(to_number(AMOUNT)) AS VARAMOUNT from ORDER

let me know how it works

Vadivel · December 29th, 2004, 09:34 AM

I tested this in SQL Server 2000:

I have an Varchar column by name "age". The below query would SUM the column value and display.

Select Sum(Convert(int, age)) From CustomerData

Best Regards
Vadivel

MVP ASP/ASP.NET
http://vadivel.thinkingms.com

itHighway · December 29th, 2004, 06:55 PM

Hi Vadivel,

It worked,
thanks.. thank a lot

Khush Rahoo

mat41 · December 29th, 2004, 07:41 PM

itHighway

What if someone types 'one' or 'two' etc into this field? your sum result could be incorrect. There are more reasons why a field that is to be summed should be some sort of integer. Anyway, glad you sorted it.

Wind is your friend
Matt