List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:August 22 2007 3:22am
Subject:Re: Why is the average of an int column returned as a string
View as plain text  
In the last episode (Aug 21), Eric Lommatsch said:
> 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?

They get created as decimals for me.  It's possible you've run into an
already-fixed bug.  I suggest upgrading to 5.0.45 and see if the
problem is still there.  Note that 5.0.18 is almost two years old and
later versions have fixed literally hundreds of issues.
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-x.html

mysql> create table test1 (num integer);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test1 values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create table test2 as select avg(num) from test1;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc test2;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| avg(num) | decimal(14,4) | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.0.45-debug-log | 
+------------------+
1 row in set (0.00 sec)

-- 
	Dan Nelson
	dnelson@stripped
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