Using union and count(*) together in SQL query

SqlUnion

Sql Problem Overview


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?

Sql Solutions


Solution 1 - Sql

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

Solution 2 - Sql

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;

Solution 3 - Sql

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

Solution 4 - Sql

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

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionDavid BožjakView Question on Stackoverflow
Solution 1 - SqlkrdluzniView Answer on Stackoverflow
Solution 2 - SqlSteve KassView Answer on Stackoverflow
Solution 3 - SqlVoteyDiscipleView Answer on Stackoverflow
Solution 4 - SqlezgigokdemirView Answer on Stackoverflow