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'
CREATE VIEW 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;

output:

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:

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.