How to SUM values from 2 separate tables that share the same column name in SQL

I have 2 tables that have the exact same columns but different data. The columns are ‘name’, ‘gender’ and ‘count’. The first table is called names_2014 and the second names_2015. My goal is simply to find the top 5 most popular names amongst both these tables.

I know that to get the most popular names for one table is:

SELECT name, count
FROM names_2014
ORDER BY count DESC 
LIMIT 5;

However, the closest I’ve gotten to my goal is:

SELECT name, count
FROM names_2014
UNION DISTINCT  -- I've tried UNION ALL as well
SELECT name, SUM(count)
FROM names_2015
GROUP BY name
ORDER BY count DESC
LIMIT 5

I’ve tried many similar variations to this but none of them are successful. It seems that I need to combine both of the tables, and then SUM(count) and GROUP BY name but I guess I’m not combining the tables properly. Any help is much appreciated as I’ve spent hours on this and I feel like the solution is so close but I just can’t see it. I’m new to SQL and just trying to test my limits.

Answer

You may perform the aggregation on a subquery that unions the two tables as the following:

select name, sum(count) cnt
from
(
  select name, count
  from names_2014
  union all
  select name, count
  from names_2015
 ) T
group by name
order by cnt desc
limit 5

Advertisement

Answer