CODEWITHSUNDEEP
mysqlsqlselectjoin
Niehm
Niehm

Reputation: 143

SQL combine two select across multiple tables

So I've these tables:

Table 1:

facilities   emails
Fac 1        [email protected]
Fac 2        [email protected]
Fac 1 RE     [email protected]

Table 2:

facilities  emails
fac1        [email protected]
fac2        [email protected]

Now, on those two tables, I am trying to figure out how many facilities are using each address email. I have two sql queries that do just that: 

SELECT ce.email, count(*)  
AS TOTAL_FACILITIES 
FROM table1 
AS ce 
GROUP BY ce.email
ORDER BY TOTAL_FACILITIES DESC

SELECT ca.emails, count(*)  
AS TOTAL_FACILITIES 
FROM table2 
AS ca 
GROUP BY ca.emails
ORDER BY TOTAL_FACILITIES DESC

This gives me the different emails from each table and how many facilities use them. I'm now trying to combine it into a single call using a join but I can't seem to figure out how to make the count work.

Any suggestions?

Upvotes: 0

Views: 38

Answers (2)

Vash
Vash

Reputation: 1787

I think you are looking for something like this: 

SELECT A.emails, COUNT(*) as total_facilities
FROM
(SELECT * FROM table1 UNION ALL SELECT * FROM table2) A
GROUP BY A.emails
ORDER BY COUNT(*) DESC;

Upvotes: 0

Eric
Eric

Reputation: 3257

You may want to UNION it instead.

SELECT ce.email, count(*) AS TOTAL_FACILITIES 
FROM table1 AS ce 
GROUP BY ce.email
UNION
SELECT ca.emails, count(*) AS TOTAL_FACILITIES 
FROM table2 AS ca 
GROUP BY ca.emails
ORDER BY TOTAL_FACILITIES DESC

A better solution is to UNION it before aggregating.

SELECT email, COUNT(*) AS TOTAL_FACILITIES
FROM (
    SELECT facilities, email
    FROM table1
    UNION ALL
    SELECT facilities, email
    FROM table2
)
GROUP BY email
ORDER BY TOTAL_FACILITIES DESC

Upvotes: 1

Related Questions