CODEWITHSUNDEEP
mysqlcount
user5358888
user5358888

Reputation: 207

mysql count from two tables and give combined count of string

I have two tables where common column is location,i am looking for the count of the locations from both the tables and display a combined count.

    Table-1 
+-------------+
|  Location   |
+-------------+
|    USA      |
|   INDIA     |
|    UK       |
|   INDIA     |
+-------------+

   Table -2
+-------------+
|  Location   |
+-------------+
|   INDIA     |
|   INDIA     |
|    USA      |
+-------------+

Here the count from table one is :

+-------------+--------+
|  Location   | COUNT  |
+-------------+--------+
|    USA      |   1    |
|   INDIA     |   2    |
|    UK       |   1    |
+-------------+--------+

Count from table 2 is :

+-------------+--------+
|  Location   | COUNT  |
+-------------+--------+
|    USA      |   1    |
|   INDIA     |   2    |
+-------------+--------+

This count am getting by :

SELECT Location, COUNT(*) AS COUNT FROM Table-1 GROUP BY Location;
SELECT Location, COUNT(*) AS COUNT FROM Table-2 GROUP BY Location;

But i want the combined count from the two tables which looks like:

+-------------+--------+
|  Location   | COUNT  |
+-------------+--------+
|    USA      |   2    |
|   INDIA     |   4    |
|    UK       |   1    |
+-------------+--------+

Thank you in advance.

Upvotes: 2

Views: 54

Answers (2)

LKW
LKW

Reputation: 180

You can add a dummy column and then add the two columns

select tt.Location, SUM(count1+count2) from 
(
SELECT Location, COUNT(*) count1, 0 AS COUNT2 FROM Table-1 GROUP BY Location;
UNION 
SELECT Location, 0 AS COUNT1, COUNT(*) count2 FROM Table-2 GROUP BY Location;
)tt
group by tt.Location

At a result, count1 is the total in Table-1 and count2 is the total in Table-2.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522299

You can try taking a UNION ALL between the two tables, and then compute the counts using GROUP BY as you were previously:

SELECT t.Location, COUNT(*) AS COUNT
FROM
(
    SELECT Location FROM Table-1
    UNION ALL
    SELECT Location FROM Table-2
) t
GROUP BY t.Location

Upvotes: 5

Related Questions