Geek Answers Handbook

To give up or not to give up?

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

I have three questions:

1.

One of the queries has this select statement:

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

Is 1 in AVG(mytable.foo, 1) AS 'myaverage' legal? I can not find documentation to support its use?

2.

The result of this gives me averages of up to two decimal places, why ?.

3.

I use this to create a temporary 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 in this column, I get an output that indicates that this average is stored as a row, so the result looks like this:

9.3

11.1

To get around this, what should I use?

Should I use CAST or CONVERT since DECIMAL (which I read is mostly binary) itself BINARY or UNSIGNED?

Or is there a way to indicate that myaverage should be an integer when I call it in an AS statement?

Sort of:

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

Thanks.

+4
source share
3 answers

Just for anyone interested, I had to delete or change my predecessor code, so this AVG question was wrong. The correct code was ROUND(AVG(myaverage),1) . I apologize to those who scratched their heads over my stupidity.

0
source

In the final question: can you post the exact MySQL query you are using?

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

So, even if your AVG () function returns DOUBLE , the other part of UNION can still return a string. In this 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)
+1
source

by 1. AVG () takes exactly one argument, otherwise MySQL will throw 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?

0
source

More articles:


All Articles