Answer: select COUNT(e.empCity) as TotalNoofEmp,c.cityName from
dbo.city c left outer join dbo.Employees e
on e.empCity = c.cityID
group by c.cityID,c.cityName
order by COUNT(e.empCity) desc
Explanation:
----------------
The first point to be kept in mind is all cities should come, irrespective of employees.
So we should go for outer join. (Inner join is used only for matching records).
In our example the City Kokatta does not have any employees. As per left outer join concept, the all the rows from table which is in left hand side of keyword "
left outer join " should come and the matching rows from right hand side of the keyword "
left outer join " will come.
The second point is, we total number of employees for each city. To achieve this we must go for group by clause, so that we can get the group of employees with respect to city. The keyword count gives the total number of employees.
In this line I am using Left outer join between two tables based on EmpCity which is the common column between these two tables.
from dbo.city c left outer join dbo.Employees e
on e.empCity = c.cityID
I have used alias name for City and Employees table to refer or get the columns of each table.
group by c.cityID,c.cityName
In above line I am grouping employees by citywise. Here the important point to be notes is I am grouping with cityId column of City table not with employee table. Because for non matching records of cityID of Employee's table will not have value.
In the following line I am soring by number of count using
Orderby clause
order by COUNT(e.empCity) desc
Finally the select clause will return the result set.
select COUNT(e.empCity) as TotalNoofEmp,c.cityName
The output will be
TotalNoofEmp cityName
5 New Delhi
4 Chennai
3 Mumbai
0 Kolkatta