Using union and count(*) together in SQL query

sql union

David Božjak · Aug 12, 2009 · Viewed 125.7k times · Source

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?

Answer

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

Using union and count(*) together in SQL query

Answer

Related questions