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 ```

Accepted Answer

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;
```

Display Summary Result in SQL Server

Solution

Problem