Search code examples
sqlunion

Using union and count(*) together in SQL query

I have a SQL query, looks something like this:

select name, count (*) from Results group by name order by name

and another, identical which loads from a archive results table, but the fields are the same.

select name, count (*) from Archive_Results group by name order by name

How would I combine the two in just one query? (So the group by would still function correctly). I tried with union all, however it won't work. What am I missing?

Solution 
  • SELECT tem.name, COUNT(*) 
FROM (
  SELECT name FROM results
  UNION ALL
  SELECT name FROM archive_results
) AS tem
GROUP BY name
ORDER BY name