﻿ SQL - View that shows the number of salesman in each city

# 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: