How to join two count queries together in one?

Question

I have two tables that I need to get a count of entries per person summed between the two tables.

I need to join the following queries together in one grouped by the name.

select count(entryid) as table1count, reqname1 from table1

And the second query:

select count(comid) as table2count, reqname2 from table2

How would I put these together so that I ended up with output like this:

jsmith 236
jsnow 13
etc.

Sometimes the same reqname will be in both tables, but it could only be in one or the other. So I want to show all reqnames between the two tables and their counts summed between the two tables.

Any ideas on how to do this?

kazzi · Accepted Answer · 2017-08-28 17:16:45Z

2

You can use UNION ALL:

select count(entryid) as table1count, reqname1 from table1
UNION ALL
select count(comid) as table2count, reqname2 from table2

answered Aug 28, 2017 at 17:16

kazzi

53412 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

trincot Over a year ago

This will produce duplicate names when they occur in both tables.

trincot · Accepted Answer · 2017-08-28 17:20:57Z

1

You could use a sub query that performs a union all, and then group and count:

select name, count(id) 
from (
        select reqname1 as name, entryid as id from table1
        union all
        select reqname2, comid from table2
     ) as combined
group by name

answered Aug 28, 2017 at 17:20

trincot

357k38 gold badges282 silver badges338 bronze badges

3 Comments

spacerobot Over a year ago

One more thing. How would I work in some date range checking. Say I wanted to get the same results when either date1 is between 8/21/2017 and 8/28/2017 or date2 is between 8/21/2017 and 8/28/2017?

trincot Over a year ago

That is a completely different question: after selecting those date fields, add where date1 between #2017-08-21# and #2017-08-28# or date2 between #2017-08-21# and #2017-08-28#. If you cannot make that work, please ask a new question. The comments section is not intended for new questions. ;-)

spacerobot Over a year ago

Thanks generating a new question.

Collectives™ on Stack Overflow

How to join two count queries together in one?

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related