average multiple fields

[David Ziger]

With mysql 4.1.11, is there a way to compile the averages and standard deviations across multiple fields?

Field1 Field2 Field3

Want to be able to create a new column:
SELECT t.Field1, t.Field2, t.Field3, mean(Field1, Field2, Field3) as CalcMean
FROM mytable as t;

There are GREATEST and LEAST functions for taking the maximum and minimum, but no analog for average and standard deviation that I can find. It appears I would need to write a custom function. Also, would appear to be a fairly obvious extention.

Thanks
David Ziger

[ronverdonk]

Full support for SQL GROUP BY and ORDER BY clauses. Support for group functions (COUNT(), COUNT(DISTINCT ...), AVG(), STD(), SUM(), MAX(), MIN(), and GROUP_CONCAT()).

This is statement from the MySQL documentation see chapter The Main Features of MySQL from the "MySQL 3.23, 4.0, 4.1 Reference Manual".


Ronald :cool:

[David Ziger]

Thanks for looking at my message, but I don't think you understood the question unless I missed something in the manuals. I want to take the average or the mean of fields, row by row. Mysql has the GREATEST and LEAST functions to take max/min, row by row. But I don't see an analog for average or standard deviation. See for example:

mysql> select LEAST(LOC_ERROR_Y1,LOC_ERROR_Y2) as least_loc_error from z_all_GID3_pb_space_pw_priority LIMIT 2;
+-----------------+
| least_loc_error |
+-----------------+
| 228352 |
| 233194 |
+-----------------+
2 rows in set (0.02 sec)

mysql> select AVG(LOC_ERROR_Y1,LOC_ERROR_Y2) as avg_loc_error from z_all_GID3_pb_space_pw_priority LIMIT 2;
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 'LOC_ERROR_Y2) as least_loc_error from z_all_GID3_pb_space_pw_priority LIMIT 2' at line 1

Thanks,
David Ziger
Synopsys Inc