Combining 3 Tables and Finding Average Age in SQL

Question

I need to combine three tables and find the average age after combining the three tables using SQL. The name of the age columns in the three tables are pr16pnk.age, pr16puf.age, and pr16yag.age. I have successfully found the results when the tables are separated, but I am having trouble combining the results. Listed below is the code that I used.

SELECT AVG(pr16pnk.age) AS MeanAge
FROM pr16pnk
UNION ALL
SELECT AVG(pr16puf.age) AS MeanAge
FROM pr16puf
UNION ALL
SELECT AVG(pr16yag.age) AS MeanAge
FROM pr16yag

Answer 1

You can use two levels of aggregation. For the average of averages:

SELECT AVG(MeanAge)
FROM (SELECT AVG(pr16pnk.age) AS MeanAge
      FROM pr16pnk
      UNION ALL
      SELECT AVG(pr16puf.age) AS MeanAge
      FROM pr16puf
      UNION ALL
      SELECT AVG(pr16yag.age) AS MeanAge
      FROM pr16yag
     ) a;

However, what you might really want is the overall average, which would be calculated as:

SELECT AVG(MeanAge),
       SUM(sum_age) / SUM(cnt)
FROM (SELECT AVG(pr16pnk.age) AS MeanAge, SUM(age) as sum_age, COUNT(*) as cnt
      FROM pr16pnk
      UNION ALL
      SELECT AVG(pr16puf.age) AS MeanAge, SUM(age) as sum_age, COUNT(*) as cnt
      FROM pr16puf
      UNION ALL
      SELECT AVG(pr16yag.age) AS MeanAge, SUM(age) as sum_age, COUNT(*) as cnt
      FROM pr16yag
     ) a;