How to join counts of 3 different tables and grouping them with a common column name in all 3 tables

I have 3 tables with same columns, I need to get count of rows with respect to packname column for all the 3 tables.

Below are the input and output table data.

TABLE 1     

CALLERID    PACKANME    
9882000342  DOC 
9882000560  DOC 
9882000945  RAJ 
9882002655  CAR 
9882003225  CAR 

TABLE 2     

CALLERID    PACKANME    
9882000342  DOC 
9882000560  DOC 
9882000945  RAJ 
9882002655  CAR 

TABLE 3     

CALLERID    PACKANME    
9882000342  DOC 
9882000560  DOC 
9882000945  RAJ 

OUTPUT      

PACKNAME    COUNT TAB2  COUNT TAB3
DOC            2                2
RAJ            1                1
CAR            1                0

I have tried below query and stopped here, I beleive we have use pivot to get the desired output but unable to do that.Please help me out.

Select 'TableA' as 'TableName',tbla.packname as Ptype,Count(tbla.num) as MobCnt from t开发者_高级运维bla
Group By tbla.packname 
Union all
Select 'TableB' as 'TableName',tblb.packname  as Ptype,Count(tblb.num)as MobCnt from tblb
Group By tblb.packname 
Union all
Select 'TableC' as 'TableName',tblc.packname  as Ptype,Count(tblc.num)as MobCnt from tblc
Group By tblc.packname 

---

First off, generate a list of distinct packnames across all tables. Then outer join a count of each table onto this. This way, you can read zero counts correctly.

SELECT
     list.packname,
     ISNULL(A.ACnt, 0) AS Tblacount,
     ISNULL(B.BCnt, 0) AS Tblbcount,
     ISNULL(C.CCnt, 0) AS Tblccount
FROM
    (
    SELECT packname FROM tbla
    UNION
    SELECT packname FROM tblb
    UNION
    SELECT packname FROM tblc
    ) list
    LEFT JOIN
    (
    Select tbla.packname as Ptype, Count(*) as ACnt from tbla
    Group By tbla.packname
    ) A ON list.packname = A.Ptype
    LEFT JOIN
    (
    Select tblb.packname as Ptype, Count(*) as BCnt from tblb
    Group By tblb.packname
    ) B ON list.packname = B.Ptype
    LEFT JOIN
    (
    Select tblc.packname as Ptype, Count(*) as CCnt from tblc
    Group By tblc.packname
    ) C ON list.packname = C.Ptype

---

Assuming you don't have a canonical source for all possible packname values:

select packname ,
       count( distinct t1.callerid ) ,
       count( distinct t2.callerid ) ,
       count( distinct t3.callerid )
from (       select packname from table1
       union select packname from table2
       union select packname from table3
     ) t
left join table1 t1 on t1.packname = t.packname
left join table2 t2 on t2.packname = t.packname
left join table3 t3 on t3.packname = t.packname
group by packname
order by packname

Aggregate functions with the exception of count(*) exclude null values from consideration.

If you do have a canonical source for the domain of packname:

select packname ,
       count( distinct t1.callerid ) ,
       count( distinct t2.callerid ) ,
       count( distinct t3.callerid )
from      packname_source t
left join table1          t1 on t1.packname = t.packname
left join table2          t2 on t2.packname = t.packname
left join table3          t3 on t3.packname = t.packname
group by packname
order by packname

Cheers!