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?
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
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...
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
Source: Stackoverflow.com