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?

Dormie

Answer 1

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

Onlybegotten

Thank you. All what I was missing was the "as tem" part ... Forgot that I have to name the "table" I create in order for this to work. – Yeasty

This will give the wrong answer. In fact, it will give a count of 1 for every name, because UNION is by default UNION DISTINCT. Use UNION ALL. – Lifework

Thank you Steve Kass, however I already knew that I needed to use UNION ALL. As stated above all what I was missing was the "as" term. – Yeasty

@Rekreativc: No problem. I commented because you marked the solution as Best Answer, and I didn't want future readers to think it was a correct answer. – Lifework

Answer 2

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;

Lifework

FYI that I hit 'Both' as a reserved word. MySQL 5.1 (btw) – Brigade

I think instead of maintaining two columns, SUM(Count) would work as well. – Dromond

Answer 3

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

Epistemology

what about case 2? – Teague

For #2, you're probably better off just running two queries and consolidating them in software. But you could also just do SELECT name, COUNT(1) FROM Results GROUP BY name UNION SELECT name, COUNT(1) FROM Archive_Results if you absolutely had to union the two. – Epistemology

Answer 4

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

Aitchbone

What does your answer add to the preceding ones? – Silvereye

Answer 5

SELECT SUM(*) 
FROM (
  SELECT COUNT(*) AS cnt FROM IP_IRRADIATE_SUBMISSION_PHOTON WHERE IRRADIATESUBMISSIONMASTERID = 84                        
  UNION SELECT COUNT(*) AS cnt FROM IP_IRRADIATE_SUBMISSION_ELECTRON WHERE IRRADIATESUBMISSIONMASTERID = 84
);

This worked in Oracle. The "As TMP" was throwing it. Slightly different query in that in sums several rows of counts.

Surprint