Display Summary Result in SQL Server

Question

I have the following table structure also I have mention my expected output please help me with query as I don't know much about SQL query

Table 1 : Category

Name      CatId   
 A         1   
 B         2   
 C         3   

Table 2 : Emp Details

FName      Id   Dob           CatId
Pratik      1   1958-04-06      2
Praveen     3   1972-05-12      1 
Nilesh      2   1990-12-12      2

So far I have tried to get all result with:

SELECT A.Code,A.EmpName,A.DOB,B.cname 
FROM EMPMASTER A
JOIN CATMASTER B ON A.cCode = B.ccode AND A.Compcode = B.CompCode
WHERE A.compcode = 'C0001' AND  month(A.DOB) >= 1 
      AND MONTH(A.DOB) <= 12  AND A.termflag='L' 
ORDER BY  A.DOB 

But my problem is, I also want summary results to be displayed

Expected Summary Output :

Grouping           No Of Employees
 A                     1
 B                     2
 C                     0

Answer 1

I think you can use LEFT JOIN, GROUP BY and COUNT as follows:

SELECT  [Grouping] = c.Name,
        [No Of Employees] = COUNT(e.ID)
FROM    Category AS c
        LEFT JOIN EmpDetails AS e
            ON e.CatId = c.CatId   
GROUP BY c.Name;

Answer 2

TRY THIS:

 SELECT A.NAME, 
 (SELECT COUNT(*) FROM #EMP B WHERE A.CATID = B.CATID) AS COUNT  
 FROM #TEMP A