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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

How to combine date from one field with time from another field - MS SQL Server

You can simply add the two.

  • if the Time part of your Date column is always zero
  • and the Date part of your Time column is also always zero (base date: January 1, 1900)

Adding them returns the correct result-

SELECT Combined = MyDate + MyTime FROM MyTable

Ref: https://bit.ly/3wldJYf


We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook