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?

score 113 · Accepted Answer · edited Sep 29 '19 at 12:19

113

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

edited Sep 29 '19 at 12:19

Artyom Ionash

405
7
17

answered Aug 12 '09 at 14:52

krdluzni

799
2
9
10

3

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. – David Božjak Aug 12 '09 at 15:06
8

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. – Steve Kass Aug 12 '09 at 19:59
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. – David Božjak Aug 13 '09 at 10:55
2

@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. – Steve Kass Aug 13 '09 at 16:47

score 11 · Answer 2 · edited Sep 29 '19 at 13:09

11

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;

edited Sep 29 '19 at 13:09

Artyom Ionash

405
7
17

answered Aug 12 '09 at 20:06

Steve Kass

7,144
20
26

1

FYI that I hit 'Both' as a reserved word. MySQL 5.1 (btw) – Douglas Aug 03 '13 at 01:24
I think instead of maintaining two columns, SUM(Count) would work as well. – Rana Jul 19 '14 at 18:25

score 9 · Answer 3 · answered Aug 12 '09 at 14:51

9

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

answered Aug 12 '09 at 14:51

VoteyDisciple

37,319
5
97
97

what about case 2? – Surya Mar 30 '20 at 06:05
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. – VoteyDisciple Mar 30 '20 at 11:23

score 2 · Answer 4 · answered Dec 15 '20 at 21:18

2

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

answered Dec 15 '20 at 21:18

ezgigokdemir

41
5

1

What does your answer add to the preceding ones? – greybeard Dec 15 '20 at 21:46

Rob · Answer 5 · 2023-03-04T15:25:10.593

0

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.

edited Mar 04 '23 at 15:25

answered Mar 04 '23 at 10:04

Rob

2,363
7
36
54

Using union and count(*) together in SQL query

5 Answers5

Linked