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
Advertisement
Answer
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;