SQL Avg function and ColdFusion Val Ffunction

[cshncn]

Hi,

I have a problem with the following SQL query:

<cfquery datasource=&quot;#db#&quot;>
SELECT Field1, Avg(Val(Field2)) AS avg_field2
FROM Table1
GROUP BY Field1
</cfquery>

The above query works fine for the Access database, in which the data type of Filed2 is Text. After I exported the Access database to SQL Server, the datatype of Field2 was changed to nvarchar, and the above query doesn't work. It first says &quot;Val&quot; is not a valid function. I changed it to Avg(#Val(Field2)#), then ColdFusion is unable to determine the value of &quot;Feild2&quot;. I replaced Val with Evaluate, the same thing.

I know this is a simple question, but can anyone help me out? Thanks!

cshncn

 

[philhege]

cshncn,

Try

<cfquery datasource=&quot;#db#&quot;>
SELECT Field1, Avg(CAST(Field2 as int)) AS avg_field2
FROM Table1
GROUP BY Field1
</cfquery>

or CAST(Field2 as numeric(x,y)) if you have decimal values.

Read BOL for more info about CAST and CONVERT.

HTH,
PH

 

[cshncn]

Thanks, Philhege. The cast function is T-SQL specific. I want to the code to work for different database, like Access, SQL Server, Oracle, etc. So are any there any SQL functions that do the job but not database specific?

Thanks again!

cshncn