Compute stats across tables

[Son Nguyen]

Hi,

I have this query:

SELECT AVG(balance) FROM accountsABC

then I want to collect the same stats for other table (same schema, just different data). Eg: accountsABC, accountsDEF....

How could I do this with just one query? Thanks.

[MattR]

You would have to UNION them together:

SELECT AVG( balance ) 
  FROM t1
UNION
SELECT AVG( balance ) 
  FROM t2
etc.

Or use subqueries:

SELECT ( SELECT AVG( balance )
           FROM t1 ) AS t1bal,
       ( SELECT AVG( balance )
           FROM t2 ) AS t2bal,
etc.

[Son Nguyen]

It seems that until MySQL 4.0 it supports UNION, but my version is 3.23.36. And I'm not sure if MySQL support sub selects (it does, but in a different form I think). Tough luck! A script will do, but a query is certainly nicer

Thanks Matt