SQL Exercises: View that shows the number of salesman in each city

SQL VIEW : Exercise-10 with Solution

10. From the following table, create a view to count the number of salespeople in each city. Return city, number of salespersons.

Sample table: salesman

Sample Solution:

-- Creating a VIEW named 'citynum'

-- Selecting 'city' and the count of distinct 'salesman_id' for each city from the 'salesman' table
-- Grouping the results by 'city'
AS SELECT city, COUNT (DISTINCT salesman_id)
FROM salesman
GROUP BY city;


sqlpractice-# FROM citynum;
   city   | count
 London   |     1
 New York |     1
 Paris    |     2
 Rome     |     1
          |     1
(5 rows)

Code Explanation:

The said query in SQL that creates a view called citynum that counts the number of unique salesmen in each city and returns the results in two columns, city and COUNT(DISTINCT salesman_id).
Using the COUNT function the view counts the number of unique salesman_id values for each city group.

Inventory database model:

Inventory database model

Contribute your code and comments through Disqus.

Previous SQL Exercise: View to show all customers with the highest grade.
Next SQL Exercise: View to show average and total orders for each salesman.

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.