Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: Single average value from two tables

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •