List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:August 22 2007 3:31am
Subject:Re: Why is the average of an int column returned as a string
View as plain text  
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
Thread
Why is the average of an int column returned as a stringEric Lommatsch21 Aug
  • Re: Why is the average of an int column returned as a stringBaron Schwartz22 Aug
    • Re: Why is the average of an int column returned as a stringPaul DuBois22 Aug
  • Re: Why is the average of an int column returned as a stringDan Nelson22 Aug
RE: Why is the average of an int column returned as a stringRhys Campbell22 Aug
RE: Why is the average of an int column returned as a stringEric Lommatsch22 Aug
RE: Why is the average of an int column returned as a stringRhys Campbell23 Aug