Using union and count together in SQL query

Question

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

User · Accepted Answer

SELECT tem name  COUNT     FROM     SELECT name FROM results   UNION ALL   SELECT name FROM archive results   AS tem GROUP BY name ORDER BY name

User · Answer

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

User · Answer

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  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

User · Answer

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

[sql] Using union and count(*) together in SQL query

Examples related to sql

Examples related to union