CODEWITHSUNDEEP
mysqlsqldatabasegroup-bycount
Hashem H Ramadan
Hashem H Ramadan

Reputation: 15

Getting Distinct Count from two tables

I have two tables for example table1 as 

╔══════════╦════════════╗
║    id    ║ login_id   ║
╠══════════╬════════════╣
║        1 ║          2 ║
║        2 ║          2 ║
║        3 ║          2 ║
║        4 ║          1 ║
╚══════════╩════════════╝

and table2 as 

╔══════════╦════════════╗
║    id2   ║ login_id   ║
╠══════════╬════════════╣
║        1 ║          3 ║
║        2 ║          1 ║
║        3 ║          2 ║
║        4 ║          1 ║
╚══════════╩════════════╝

I need to get the count of column login_id from the two tables where there is no duplicate

I have tried 

SELECT COUNT(DISTINCT t1.login_id), COUNT(DISTINCT t2.login_id) FROM table1 as t1 JOIN table2 AS t2

I got COUNT(DISTINCT t1.login_id) 2 COUNT(DISTINCT t2.login_id) 3.

Is there any way that I can the output to be only 3. Hope that it's clear to understand what I want.

Upvotes: 1

Views: 53

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use UNION :

select count(*)
from (select login_id 
      from table1 union -- WILL REMOVE DUPLICATE
      select login_id 
      from table2
     ) t;

Upvotes: 3

GMB
GMB

Reputation: 222432

You can use union all and aggregation:

select count(distinct login_id) no_distinct_logins
from (
    select login_id from table1
    union all select login_id from table2
) t

Upvotes: 2

Related Questions