RE: Why is the average of an int column returned as a string
I'd guess this may be a funny related to Crystal Reports as I don't recall experiencing this myself. Rhys -Original Message- From: Eric Lommatsch [mailto:[EMAIL PROTECTED] Sent: 22 August 2007 22:21 To: Rhys Campbell; mysql@lists.mysql.com Subject: RE: Why is the average of an int column returned as a string I have tried that and that seems to be working for me. I am just wondering why all of the sudden queries that were returning the averages as numeric values as string values. Thank you for your suggestion. Thank you Eric H. Lommatsch Programmer 360 Business 2087 South Grant Street Denver, CO 80210 Tel 303-777-8939 Fax 303-778-0378 [EMAIL PROTECTED] -Original Message- From: Rhys Campbell [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 22, 2007 3:34 AM To: Eric Lommatsch; mysql@lists.mysql.com Subject: RE: Why is the average of an int column returned as a string You could use the CAST function, although I have just discovered that MySQL is rather limited in the types you are able to CAST to... http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html -Original Message- From: Eric Lommatsch [mailto:[EMAIL PROTECTED] Sent: 21 August 2007 22:52 To: mysql@lists.mysql.com Subject: Why is the average of an int column returned as a string 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 [EMAIL PROTECTED] This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why is the average of an int column returned as a string
You could use the CAST function, although I have just discovered that MySQL is rather limited in the types you are able to CAST to... http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html -Original Message- From: Eric Lommatsch [mailto:[EMAIL PROTECTED] Sent: 21 August 2007 22:52 To: mysql@lists.mysql.com Subject: Why is the average of an int column returned as a string 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 [EMAIL PROTECTED] This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why is the average of an int column returned as a string
I have tried that and that seems to be working for me. I am just wondering why all of the sudden queries that were returning the averages as numeric values as string values. Thank you for your suggestion. Thank you Eric H. Lommatsch Programmer 360 Business 2087 South Grant Street Denver, CO 80210 Tel 303-777-8939 Fax 303-778-0378 [EMAIL PROTECTED] -Original Message- From: Rhys Campbell [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 22, 2007 3:34 AM To: Eric Lommatsch; mysql@lists.mysql.com Subject: RE: Why is the average of an int column returned as a string You could use the CAST function, although I have just discovered that MySQL is rather limited in the types you are able to CAST to... http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html -Original Message- From: Eric Lommatsch [mailto:[EMAIL PROTECTED] Sent: 21 August 2007 22:52 To: mysql@lists.mysql.com Subject: Why is the average of an int column returned as a string 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 [EMAIL PROTECTED] This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is the average of an int column returned as a string
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is the average of an int column returned as a string
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 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is the average of an int column returned as a string
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]
