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