To cast or not to cast?

I am developing a system using MySQL queries written by another programmer, and am adapting his code.

I have three questions:

1.

One of the queries has this select statement:

SELECT
   [...]
   AVG(mytable.foo, 1) AS 'myaverage'`, 

Is the 1 in AVG(mytable.foo, 1) AS 'myaverage' legitimate? I can find no documentation to support its usage?

2.

The result of this gives me average values to 2 decimal places, why?.

3.

I am using this to create a temp table. So:

(SELECT
    [...]
    AVG(`mytable`.`foo`, 1) AS `myaverage`,
FROM
    [...]
WHERE
    [...]
GROUP BY
    [...])
UNION
(SELECT
    [...]
FROM
    [...]
WHERE
    [...]
GROUP BY
    [...])
) AS `tmptable`
ORDER BY
    `tmptable`.`myaverage` DESC

When I sort the table on this column I get output which indicates that this average is being stored as a string, so the result is like:

9.3

11.1

In order to get around this what should I use?

Should I be using CAST or CONVERT, as DECIMAL (which I read is basically binary), BINARY itself, or UNSIGNED?

Or, is there a way to state that myaverage should be an integer when I name it in the 开发者_开发技巧AS statement?

Something like:

SELECT
    AVG(myaverage) AS `myaverage`, INT(10)

Thanks.

On your last question: can you post the exact MySQL query that you are using?

The result type of a column from a UNION is determined by everything you get back. See http://dev.mysql.com/doc/refman/5.0/en/union.html .

So, even if your AVG() function returns a DOUBLE, the other part of the UNION may still return a string. In which case the column type of the result will be a string.

See the following example:

mysql> select a from (select 19 as a union select '120') c order by a;
+-----+
| a   |
+-----+
| 120 |
| 19  |
+-----+
2 rows in set (0.00 sec)

mysql> select a from (select 19 as a union select 120) c order by a;
+-----+
| a   |
+-----+
|  19 |
| 120 |
+-----+
2 rows in set (0.00 sec)

Just for anyone who's interested, I must have deleted or changed my predecessors code so this AVG question was incorrect. The correct code was ROUND(AVG(myaverage),1). Apologies to those who scrathed their heads over my stupidity.

on 1. AVG() accepts exactly one argument, otherwise MySQL will raise an error:

mysql> SELECT AVG( id, 1 ) FROM anytable;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 1 )' at line 1

http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_avg

Just because I'm curious - what should the second argument do?