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.

businessentityid|addressid|addresstypeid|rowguid                             |modifieddate           |
----------------+---------+-------------+------------------------------------+-----------------------+
               1|      249|            2|3a5d0a00-6739-4dfe-a8f7-844cd9dee3df|2014-09-12 11:15:06.967|
               2|      293|            2|84ae7057-edf4-4c51-8b8d-3aeaefbfb4a1|2014-09-12 11:15:06.967|
               3|      224|            2|3c915b31-7c05-4a05-9859-0df663677240|2014-09-12 11:15:06.967|
               4|    11387|            2|3dc70cc4-3ae8-424f-8b1f-481c5478e941|2014-09-12 11:15:06.967|
               5|      190|            2|c0ed2f68-937b-4594-9459-581ac53c98e3|2014-09-12 11:15:06.967|
               6|      286|            2|4ca1686a-a7df-4bd8-9d7d-82a63210208a|2014-09-12 11:15:06.967|
               7|       49|            2|1528e305-3e34-4dea-bdd7-c7ddcdd11ef8|2014-09-12 11:15:06.967|
               8|      230|            2|38f80f8f-5ca7-4d06-aefa-cd930a0a7b3f|2014-09-12 11:15:06.967|
               9|      187|            2|51c9d232-dd34-49a5-8442-f269e0b9a6ff|2014-09-12 11:15:06.967|
              10|    11386|            2|13981fc6-9688-49c8-aa1e-80c7f28ea2ff|2014-09-12 11:15:06.967|
              11|    32505|            2|1f216434-3714-4bfb-9b05-4be77ebcce3f|2014-09-12 11:15:06.967|
              12|        1|            2|8aa698fc-090f-42ee-a197-2e7f7394d9f1|2014-09-12 11:15:06.967|
			  -- more --

Click to view Full table

Sample Solution:

-- Selecting the city and counting the number of employees in each city
SELECT a.City, COUNT(b.AddressID) NoOfEmployees 
-- Retrieving data from the 'BusinessEntityAddress' table aliased as 'b'
FROM Person.BusinessEntityAddress AS b   
    -- Joining the 'BusinessEntityAddress' table with the 'Address' table aliased as 'a'
    INNER JOIN Person.Address AS a  
        ON b.AddressID = a.AddressID  
-- Grouping the results by city
GROUP BY a.City  
-- Ordering the results by city
ORDER BY a.City;

Explanation:

• The SELECT statement retrieves the city and counts the number of employees in each city.
• FROM Person.BusinessEntityAddress AS b specifies the 'BusinessEntityAddress' table and aliases it as 'b'.
• The INNER JOIN clause joins the 'BusinessEntityAddress' table with the 'Address' table aliased as 'a' based on the AddressID.
• The GROUP BY clause groups the results by city, allowing the count of employees per city.
• ORDER BY a.City orders the results by city in ascending order.

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.