MySQL-Count from 2 tables

Question

I have 2 tables. Table table1 like this

specialty     doctor
  1              A
  1              B
  2              A
  2              C

Table table2 like this:

specialty     doctor
  1              A
  1              D
  2              C

I want to count distinct doctor from both the table group by specialty and have the output like this

specialty      doctor
  1              3
  2              2

(specialty 1 has 3 doctors:A, B, D; specialty 2 has 2 doctors: A, C)

Really appreciate your help

Gordon Linoff · Accepted Answer · 2015-07-02 11:03:12Z

1

You can use union to bring the tables together and then group by:

select specialty, count(*)
from ((select specialty, doctor from table1) union
      (select specialty, doctor from table2)
     ) t
group by specialty;

Note the use of union here and not union all. You specifically want to eliminate duplicates.

answered Jul 2, 2015 at 11:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL-Count from 2 tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related