count record from two tables which has no relation

Question

I have two tables tbl_user1 and tbl_user2 both are field name are same but there is no relation between that tables now I want to find total referred count from both table
for example...

tbl_user1
-----------------------
UID   | referenceBy      |  firstName  |   lastName  |  emailAddress
----------------------------------------------------------------------------
1     | NULL             | aa1         | ab1         | aa1@email.com
2     | aa1@email.com    | aa2         | ab2         | aa2@email.com
3     | NULL             | aa3         | ab3         | aa3@email.com
4     | aa2@email.com    | aa4         | ab4         | aa4@email.com
5     | aa2@email.com    | aa5         | ab5         | aa5@email.com
6     | bb1@email.com    | aa6         | ab6         | aa6@email.com
7     | bb2@email.com    | aa7         | ab7         | aa7@email.com
8     | bb3@email.com    | aa8         | ab8         | aa8@email.com
9     | bb3@email.com    | aa9         | ab9         | aa9@email.com

and second one table is somthing like...

tbl_user2
-----------------------
UID   | referenceBy      |  firstName  |   lastName  |  emailAddress
----------------------------------------------------------------------------
1     | NULL             | bb1         | bc1         | bb1@email.com
2     | bb1@email.com    | bb2         | bc2         | bb2@email.com
3     | NULL             | bb3         | bc3         | bb3@email.com
4     | bb3@email.com    | bb4         | bc4         | bb4@email.com
5     | bb2@email.com    | bb5         | bc5         | bb5@email.com
6     | bb1@email.com    | bb6         | bc6         | bb6@email.com
7     | aa2@email.com    | bb7         | bc7         | bb7@email.com
8     | aa3@email.com    | bb8         | bc8         | bb8@email.com
9     | bb5@email.com    | bb9         | bc9         | bb9@email.com

now, as you can see there is no relation between these two tables and I want result like following..

MAIN_RESULT_THAT_I_WANT
-----------------------
referenceEmail   |  referenceEmailCount 
----------------------------------------------------------------------------
aa1@email.com    | 1
aa2@email.com    | 3
aa3@email.com    | 1
aa4@email.com    | 0
aa5@email.com    | 0
aa6@email.com    | 0
aa7@email.com    | 0
aa8@email.com    | 0
aa9@email.com    | 0
bb1@email.com    | 3
bb2@email.com    | 2
bb3@email.com    | 3
bb4@email.com    | 0
bb5@email.com    | 1
bb6@email.com    | 0
bb7@email.com    | 0
bb8@email.com    | 0
bb9@email.com    | 0

here in result all emailAddress of all user and total of how many user(s) registered by that particular emailAddress.

Answer 1

Since the schema is same for 2 tables so you can perform union to get combined results and can perform an outer query to get the total count.

select referenceEmail, count(*)  as referenceEmailCount from (
select * from table1
union all 
select * from table2

) as alias 
group by alias.referenceEmail

Answer 2

I am guessing that the result you want is just copy and pasted since it seems inaccurate. Like HoneyBadger says it is strange that aa6 is missing and still in the result, that indicates you have another list you are not telling us about? Or you just write the result in notepad...

If you just want a list of emails and count this will work:

select referenceBy, count(1) as referenceEmailCount from (
  select referenceBy from tbl_user1
  union all
  select referenceBy from tbl_user2
) as t
group by referenceBy

Otherwise give us more info if this is not what you need.