Using union and count(*) together in SQL query
SqlUnionSql 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...
- To count all the instances of "Bob Jones" in both tables (for example)
- To count all the instances of "Bob
Jones" in
Results
in one row and all the instances of "Bob Jones" inArchive_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