Solved

Sum CHAR DataType in SQL

Posted on 2004-10-13
7
2,698 Views
Last Modified: 2008-03-10
How can I Sum a CHAR DataType in SQL????????
The Data that I have in my table is like this
Field1
---------
1400.00
1521.00
1523.35
.
.
.
Even it looks like number it is not it was specified as CHAR in the Tbale's Dising
0
Comment
Question by:amedexitt
7 Comments
 
LVL 6

Expert Comment

by:RaisinJ
Comment Utility
Convert(INT,@Field)

Select
    Sum(Convert(INT,Field1)) As Field1
From
    Table!

The only time this will give you a problem is if there are non numeric charaters in the field...
0
 
LVL 6

Assisted Solution

by:RaisinJ
RaisinJ earned 250 total points
Comment Utility
Sorry,

Noticed your Fields has decimals... In that case, you'll receive and error when converting to INT, you want to Convert to Decimal...

Convert(Decimal(10,2),Field)

0
 
LVL 5

Expert Comment

by:waelothman
Comment Utility
select sum(cast (fieldname as real)) from tablename
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 1

Expert Comment

by:AntonG
Comment Utility
although the above comments are pretty much correct.... I would rather do the conversion to a float...

i.e. SELECT SUM(CONVERT(float, colname)) FROM tablename
0
 
LVL 7

Expert Comment

by:sukumar_diya
Comment Utility
You can try this also

select sum(cast(columnname as float)) from tablename
0
 
LVL 8

Accepted Solution

by:
MartinCMS earned 250 total points
Comment Utility
try...

select sum(convert(numeric(8,2),Field1))
from yourTable
0
 
LVL 5

Expert Comment

by:svid
Comment Utility
To be on the safer side you may want to add

where isnumeric(Field1) = 1 to the select statement so you don't get a syntax error if there are non-numeric values
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now