Convert Char data type to number

6 Apr 2006 4:27 PM

Chris

I need to covert Char data type to number

I am running a query and I am getting the following error

can some one help with this

ERROR
Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a char data type as an
argument.

Query

SELECT

current_month_09_2005b.corp,
current_month_09_2005b.sysid,
SUM(current_month_09_2005b.average_balance) AS [Average Balance],
SUM(current_month_09_2005b.BookAmount) AS [Book Amount],
SUM(P2_AFS.[P02_031_AVERAGE-BALANCE]) AS [AFS Average Balance],
SUM(P2_AFS.[P02_002_CURRENT-BOOK-PRINCIPAL]) as [AFS Current Book]

FROM current_month_09_2005b INNER JOIN
     P2_AFS ON current_month_09_2005b.corp = P2_AFS.[P02_039_CORP-NUM] AND
     current_month_09_2005b.gl_prin_rc_num =
P2_AFS.[P02_051_RESP-CNTR-NUM-PRIN] AND
     current_month_09_2005b.sysid = P2_AFS.P02_056_SUBSYSID

Group By

current_month_09_2005b.corp,
current_month_09_2005b.sysid,
current_month_09_2005b.average_balance,
current_month_09_2005b.BookAmount,
P2_AFS.[P02_031_AVERAGE-BALANCE],
P2_AFS.[P02_002_CURRENT-BOOK-PRINCIPAL]

6 Apr 2006 4:36 PM

Mike Labosh

>I need to covert Char data type to number
>
> I am running a query and I am getting the following error
>
> can some one help with this

Look in SQL Server Books Online for the CAST() and CONVERT() functions.
That will handle it for you. Just watch out that your data is actually
numeric, because doing this below will fail:

SELECT SUM(CAST('A' AS INT))

--

Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com

"Escriba coda ergo sum." -- vbSensei

6 Apr 2006 4:36 PM

SQL

you need to convert to decimal/int, take a look at this

declare @chvAmount varchar(10)
select @chvAmount ='50'
select
sum(convert(decimal(12,3),@chvAmount)),sum(convert(int,@chvAmount))
select sum(@chvAmount) -- this will break

Denis the SQL Menace
http://sqlservercode.blogspot.com/

6 Apr 2006 4:40 PM

ML

Please post DDL and sample data. Also look up "CAST and CONVERT" in Books
Online.

ML

---
http://milambda.blogspot.com/

6 Apr 2006 4:48 PM

KH

To get the character code:

SELECT ASCII('A'), UNICODE('A')

Show quote

"Chris" wrote:

> I need to covert Char data type to number
>
> I am running a query and I am getting the following error
>
> can some one help with this
>
>
> ERROR
> Server: Msg 409, Level 16, State 2, Line 1
> The sum or average aggregate operation cannot take a char data type as an
> argument.
>
>
> Query
>
> SELECT
>
> current_month_09_2005b.corp,
> current_month_09_2005b.sysid,
> SUM(current_month_09_2005b.average_balance) AS [Average Balance],
> SUM(current_month_09_2005b.BookAmount) AS [Book Amount],
> SUM(P2_AFS.[P02_031_AVERAGE-BALANCE]) AS [AFS Average Balance],
> SUM(P2_AFS.[P02_002_CURRENT-BOOK-PRINCIPAL]) as [AFS Current Book]
>
>
>
> FROM current_month_09_2005b INNER JOIN
>      P2_AFS ON current_month_09_2005b.corp = P2_AFS.[P02_039_CORP-NUM] AND
>      current_month_09_2005b.gl_prin_rc_num =
> P2_AFS.[P02_051_RESP-CNTR-NUM-PRIN] AND
>      current_month_09_2005b.sysid = P2_AFS.P02_056_SUBSYSID
>
> Group By
>
> current_month_09_2005b.corp,
> current_month_09_2005b.sysid,
> current_month_09_2005b.average_balance,
> current_month_09_2005b.BookAmount,
> P2_AFS.[P02_031_AVERAGE-BALANCE],
> P2_AFS.[P02_002_CURRENT-BOOK-PRINCIPAL]