Display the final result in SQL Server

Question

I have the following table structure, also I mention my expected output, please help me with the query, since I know little 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 been trying to get the result:

 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 that I also want the final results to be displayed

Expected Outcome:

 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 IT:

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