Problem with CROSS JOIN and referencing, field list error in MySQL query.

Question

I'm trying to run the following MySQL query:

mysql> SELECT num.value, agro.mean, agro.dev
    -> FROM randomNumbers num
    -> CROSS JOIN (
    ->         SELECT AVG(value) AS mean, STDDEV(value) AS dev 
    ->         FROM randomNumbers
    ->     ) agro
    -> ORDER BY num.value;

the example came from here http://www.sitecrafting.com/blog/stats-in-mysql-pt-outliers/, randomNumbers is just a list of random numbers.

I'm getting the error: ERROR 1054 (42S22): Unknown column 'num.value' in 'field list'. When I try to debug it, I realized that I don't know what 'agro' is doing. I presume it is allowing me to reference the mean and dev with the prefix agro, but it doesn't really make sense and I don't know why the statement is not working. This statement works fine:

mysql> select num.value from randomNumbers num;

Can you help? Thanks.

Answer 1

agro is an alias for the result set created by the subselect - this is a requirement for subselects located in the FROM clause so that the columns can be referenced properly. I'm not sure why you're query isn't working correctly. Have you tried running the subselect by itself?

SELECT AVG(value) AS mean, STDDEV(value) AS dev FROM randomNumbers;

This probably won't fix anything but try adding AS before your aliases.

SELECT num.value, agro.mean, agro.dev
FROM randomNumbers AS num
    CROSS JOIN (
        SELECT AVG(value) AS mean, STDDEV(value) AS dev 
        FROM randomNumbers
    ) AS agro
ORDER BY num.value;