SQL Server: Group by, Having Clause and Sub-Query Examples

Example1

--Display Department whose employee count is less than than 3

--Stage1 (Get Employees count in each Department)

select Department,count(empid) as 'No.Of.Emps' 

from tabEmployee 

group by Department

--Output 

Development                    5

HR Department                  1

Training                      7

--Stage2 (Enhance query with having clause)

select Department,count(empid) as 'No.Of.Emps' 

from tabEmployee 

group by Department 

having count(empid)<5

--Output

HR Department                   1

Example2

--Display Designation Name whose employee the count is less than the count Behavioral Trainer Designation

--Stage1 (Get Employees-count of 'Behavioral Trainer' Designation)

select count(empid) 

from tabEmployee 

where Designation='Behavioral Trainer'

--Output 

4

--Stage2 (Get counts fo all Designations)

select Designation,count(empid) as 'No.Of.Emps' from tabEmployee group by Designation

--Output

Analyst Trainee     3

Behavioral Trainer  4

HR Manager          1

Technical Trainer   5

--Stage3 Combine above two queries to get needed results

select Designation,count(empid) as 'No.Of.Emps'

from tabEmployee

group by Designation

having count(empid)<(select count(empid)

                      from tabEmployee

                      where Designation='Behavioral Trainer')

--Output

Analyst Trainee     3

HR Manager          1