I am pretty new myself but can't you do it with a join? Like SELECT AVG(avg) FROM table_a, table_b GROUP BY id_field.
Respectfully, Ligaya Turmelle ""John McCaskey"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] First of all I am using mysql 4.0.18 and I am not free to upgrade to 4.1 or 5.0. I have two tables: table_a, and table_b these two tables have the same structure: CREATE table_a ( id_field mediumint(8) unsigned NOT NULL, avg float default NULL ) What I want to do is get the combined avg across table_a and table_b for each id value. So I tried doing (SELECT AVG(avg) FROM table_a) UNION (SELECT AVG(avg) FROM table_b) GROUP BY id_field; This however doesn't work. I can see why this is thinking about how a union should work mathematically but I'm left with no apparent way to directly get the avg across two tables. I know that I can take the avg from each along with the row count and then do a weighted average using those values, but I'd prefer not to have to do that. Any suggestions or am I stuck doing individual group bys on each table uninoning the results and then doing weighted averages on the matching pairs? Here is what I'm talking about doing with the weighted averages incase it is unclear: (SELECT AVG(avg) AS avg_a, COUNT(id_field) AS count_a FROM table_a GROUP BY id_field) UNION (SELECT AVG(avg) AS avg_b, COUNT(id_field) AS count_b FROM table_b GROUP BY id_field); Then I would match up the results and compute total_avg = (avg_a*count_a + avg_b*count_b)/(count_a+count_b). This is not nearly as clean as I would like. John A. McCaskey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
