Using union and count(*) together in SQL query

If you have supporting indexes, and relatively high counts, something like this may be considerably faster than the solutions suggested:

SELECT name, MAX(Rcount) + MAX(Acount) AS TotalCount
FROM (
  SELECT name, COUNT(*) AS Rcount, 0 AS Acount
  FROM Results GROUP BY name
  UNION ALL
  SELECT name, 0, count(*)
  FROM Archive_Results
  GROUP BY name
) AS Both
GROUP BY name
ORDER BY name;

Is your goal...

1. To count all the instances of "Bob Jones" in both tables (for example)
2. To count all the instances of "Bob Jones" in Results in one row and all the instances of "Bob Jones" in Archive_Results in a separate row?

Assuming it's #1 you'd want something like...

SELECT name, COUNT(*) FROM
(SELECT name FROM Results UNION ALL SELECT name FROM Archive_Results)
GROUP BY name
ORDER BY name

select T1.name, count (*)
from (select name from Results 
      union 
      select name from Archive_Results) as T1
group by T1.name order by T1.name

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