CODEWITHSUNDEEP
mysqlsqlgroup-byunion
Ahmad Sattar
Ahmad Sattar

Reputation: 321

UNION All, Group BY and then get overall COUNT

I have 2 Tables..

User 1

user_id     mobile_no
   1          1111
   2          2222

User 2

user_id     mobile_no
   1          3333
   2          2222

I Want to first UNION These tables, then group by and then want to count total records

I am using this query but it's not working 

SELECT COUNT(Q2.total) AS Overall
FROM (
   SELECT COUNT(Q.user_id) AS total
   FROM (
     SELECT * FROM user1
     UNION ALL 
     SELECT * FROM user2
   ) Q
  GROUP BY Q.mobile_no
) Q2

if i user Inner Query e-g:

   SELECT COUNT(Q.user_id) AS total
   FROM (
     SELECT * FROM user1
     UNION ALL 
     SELECT * FROM user2
   ) Q
  GROUP BY Q.mobile_no

I get these results, actually i want to again count these result...

total
 2
 1
 1

i expect this result

Overall
   3

Upvotes: 1

Views: 6905

Answers (5)

Anand Somasekhar
Anand Somasekhar

Reputation: 596

Try this:

SELECT COUNT(*) FROM 
   ( (SELECT * FROM user1) UNION 
      (SELECT user_id as u1,mobile_no as m1
      FROM user2) )  as a1  GROUP BY a1 .1

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166356

Rather use UNION and not UNION ALL

SQL UNION Operator

The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION.

SELECT  COUNT(mobile_no) Overall
FROM    (
            SELECT  
                    mobile_no
            FROM    User1
            UNION
            SELECT  
                    mobile_no
            FROM    User2
        ) Q

EDIT:

As @Bohemian correctly stated, no need for the distinct.

Upvotes: 0

Bohemian
Bohemian

Reputation: 424983

This is weird. No one seems to have realised it's as simple as:

select count(*) overall
from (select mobile_no from user1 union select mobile_no from user2)

The difference between union and union all is that union removes duplicates.

Upvotes: 3

hashbrown
hashbrown

Reputation: 3516

select count (distinct mobile_no) from
(select user_id, mobile_no from user1 u1
UNION ALL
select user_id, mobile_no from user2 u2
) X
group by X.mobile_no

Upvotes: 0

Mike Meyers
Mike Meyers

Reputation: 2895

Assuming that you are looking for the distinct number of mobile numbers:

select count(distinct mobile_no) as Overall
from (
  select user_id, mobile_no
  from user1
  union all
  select user_id, mobile_no
  from user2
) a

Upvotes: 0

Related Questions