AdventureWorks Database: Count employees for each city group by using multiple tables

SQL Query - AdventureWorks: Exercise-16 with Solution

16. From the following table write a query in SQL to retrieve the number of employees for each City. Return city and number of employees. Sort the result in ascending order on city.

Sample table: Person.BusinessEntityAddress

Click to view Full table

Sample Solution:

SELECT a.City, COUNT(b.AddressID) NoOfEmployees 
FROM Person.BusinessEntityAddress AS b   
    INNER JOIN Person.Address AS a  
        ON b.AddressID = a.AddressID  
GROUP BY a.City  
ORDER BY a.City;

Sample Output:

city                 |noofemployees|
---------------------+-------------+
Abingdon             |            1|
Albany               |            4|
Alexandria           |            2|
Alhambra             |            1|
Alpine               |            1|
Altadena             |            2|
Altamonte Springs    |            1|
Anacortes            |            3|
Arlington            |            1|
Ascheim              |            1|
Atlanta              |            2|
Auburn               |            1|
Augsburg             |            2|
Augusta              |            1|
Aujan Mournede       |            1|
Aurora               |            1|
Austell              |            1|
...

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Empty group as one of the elements of a GROUPING SET.
Next: Sales by year using GROUP BY with an expression.