Single average value from two tables

pearl2 · #1 (**permalink**) 12-23-04, 08:23

Hi,

I have two tables containing scores and I would like to obtain a single average score from those two tables. Is there a way to do that?

I've the sql below as follows:

Code:

sql statement
SELECT COUNT(*) AS count1, AVG(score) AS avg_score1
      FROM scores1
      WHERE id=3
      UNION ALL SELECT COUNT(*) AS count2, AVG(score) AS avg_score2
      FROM scores2
      WHERE id=3

Sample table values

Table scores1
id score
3  100
3   80

Table scores2
id score
3   50
3   80
3   80

That got me the results as follows:

Code:

Count average
2        90
3        70

I was looking for a way to get the average of all the five scores. In order words, I would like the query to produce the average score of 78 (sum of all the test scores divided by 5).

Is there a way to that?

Thanks in advance

r937 · #2 (**permalink**) 12-25-04, 09:07

requires 4.1 --

Code:

select count(*)   as count_both
     , avg(score) as avg_score_both
  from (
       select score
         from scores1
        where id = 3     
       union all
       select score
         from scores2
        where id = 3   
       ) as dt

or look up merge tables at mysql.com

pearl2 · #3 (**permalink**) 12-25-04, 21:33

Thanks, r937

Both my development and production servers are using MySQL 4.0.xx. I can't upgrade to 4.1.xx in the production server because it's a paid share server which I've no control over.

Will come back to this solution when the MySQL server is upgraded.

Internet Services

Web Development

Digital Marketing

Consumer Tech