sql - Join between two tables -

table1 - doctors

+---------+--------+------+ | country |  state |  doc | +---------+--------+------+ | india   |  ap    | 20   | +---------+--------+------+ | india   |  tn    | 30   | +---------+--------+------+ | india   |  ka    | 10   | +---------+--------+------+ |      |  la    | 30   | +---------+--------+------+ |      |  ca    | 10   | +---------+--------+------+ |      |  ny    | 50   | +---------+--------+------+ 

table2 - engineers

+---------+--------+-------+ | country |  state |  engg | +---------+--------+-------+ | india   |  ap    | 100   | +---------+--------+-------+ | india   |  tn    | 400   | +---------+--------+-------+ | india   |  ka    | 250   | +---------+--------+-------+ |      |  la    | 140   | +---------+--------+-------+ |      |  ca    | 120   | +---------+--------+-------+ |      |  ny    | 150   | +---------+--------+-------+ 

desired output:

+---------+------+-------+ | country |  doc |  engg | +---------+------+-------+ | india   | 60   | 750   | +---------+------+-------+ |      | 90   | 410   | +---------+------+-------+ 

i tried below query getting more count of docs , engg. please correct me..

select country, sum(a.doc), sum(b.engg)    table1 join table2 b on (a.country = b.country)  

you can use union all

select     country,     sum(doc) doc,     sum(engg) engg     (select         country,         doc,         0 engg              doctors      union      select         country,         0,         engg              engineers     ) group     country