At 8:25 PM -0400 8/21/07, Baron Schwartz wrote:
Eric Lommatsch wrote:
Hello,
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
form:
"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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
