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?

Thank you

Eric H. Lommatsch
Programmer
360 Business
2087 South Grant Street
Denver, CO 80210
Tel 303-777-8939
Fax 303-778-0378

ericl@360b.com

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)...

Baron

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@allantgroup.com