At 8:25 PM -0400 8/21/07, Baron Schwartz wrote:
>Eric Lommatsch wrote:
>> I am working with a query that calculates the averages of survey
>>answers. The survey answers are stored in the database as int(11)
>>fields. When I run
>>the query the results that I am getting are being returned as
>>string data. The query that I am working with is a data source for
>>a Crystal Reports
>>reports. The average columns that are being returned by the query are used in
>>the report in fields that have been formatted for double values.
>> I am using MySQL 5.0.18 as the database. What would I have to do to get the
>>averages of Int columns to return as doubles, rather then having to change
>>all of my columns to be double columns?
>I'm a little unclear where they're being returned as strings, and
>how you know they're strings and not floats. I think the math is
>done with floats, so even if your columns are floating-point, you'll
>get the same results. But in general, you can use CAST(), though
>CAST-ing to a floating-point isn't supported. I don't know why not.
>SELECT CAST(AVG(col) AS DECIMAL(9,2)...
Eric, are you using the C API binary (prepared statement) protocol?
If so, even if the values are DECIMAL, they'll be returned in string
"DECIMAL values are returned as strings, which is why the
corresponding C type is char. DECIMAL values returned by the server
correspond to the string representation of the original server-side
value. For example, 12.345 is returned to the client as '12.345'. If
you specify MYSQL_TYPE_NEWDECIMAL and bind a string buffer to the
MYSQL_BIND structure, mysql_stmt_fetch() stores the value in the
buffer without conversion. If instead you specify a numeric variable
and type code, mysql_stmt_fetch() converts the string-format DECIMAL
value to numeric form."
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com