SQL Exercises: Salesperson live in the same city as the customers

SQL SUBQUERY: Exercise-27 with Solution

27. From the following tables write a SQL query to calculate the total order amount generated by a salesperson. Salespersons should be from the cities where the customers reside. Return salesperson name, city and total order amount.

ord_no      purch_amt   ord_date    customer_id  salesman_id
----------  ----------  ----------  -----------  -----------
70001       150.5       2012-10-05  3005         5002
70009       270.65      2012-09-10  3001         5005
70002       65.26       2012-10-05  3002         5001
70004       110.5       2012-08-17  3009         5003
70007       948.5       2012-09-10  3005         5002
70005       2400.6      2012-07-27  3007         5001
70008       5760        2012-09-10  3002         5001
70010       1983.43     2012-10-10  3004         5006
70003       2480.4      2012-10-10  3009         5003
70012       250.45      2012-06-27  3008         5002
70011       75.29       2012-08-17  3003         5007
70013       3045.6      2012-04-25  3002         5001

salesman_id  name        city        commission
-----------  ----------  ----------  ----------
5001         James Hoog  New York    0.15
5002         Nail Knite  Paris       0.13
5005         Pit Alex    London      0.11
5006         Mc Lyon     Paris       0.14
5003         Lauson Hen  San Jose    0.12
5007         Paul Adam   Rome        0.13

customer_id  cust_name     city        grade       salesman_id
-----------  ------------  ----------  ----------  -----------
3002         Nick Rimando  New York    100         5001
3005         Graham Zusi   California  200         5002
3001         Brad Guzan    London      100         5005
3004         Fabian Johns  Paris       300         5006
3007         Brad Davis    New York    200         5001
3009         Geoff Camero  Berlin      100         5003
3008         Julian Green  London      300         5002
3003         Jozy Altidor  Moncow      200         5007

Sample Solution:

-- Selecting specific columns from the 'salesman' table, along with a calculated column 'total_amt' from a subquery
SELECT salesman.name, salesman.city, subquery1.total_amt
-- Specifying the tables to retrieve data from ('salesman' and a subquery aliased as 'subquery1')
FROM salesman, 
   -- Subquery 1: Selecting 'salesman_id' and the sum of 'purch_amt' from the 'orders' table, grouped by 'salesman_id'
   (SELECT salesman_id, SUM(orders.purch_amt) AS total_amt FROM orders GROUP BY salesman_id) subquery1
-- Joining the outer query's 'salesman' table with 'subquery1' based on matching 'salesman_id'
WHERE subquery1.salesman_id = salesman.salesman_id
-- Filtering the results based on the condition that 'city' is in the set of distinct 'city' values returned by a subquery
AND salesman.city IN (SELECT DISTINCT city FROM customer);

Output of the Query:

    name    |   city   | total_amt 
------------+----------+-----------
 Mc Lyon    | Paris    |   1983.43
 Nail Knite | Paris    |   1349.45
 James Hoog | New York |  11271.46
 Pit Alex   | London   |    270.65
(4 rows)

Explanation:

The said SQL query is selecting the name and city of a salesman, as well as the total amount of purchases associated with that salesman, from two tables: 'salesman' and a subquery named "subquery1". The subquery is selecting the sum of the purchase amount of all orders, grouped by the salesman's ID. The main query then joins the subquery with the 'salesman' table by matching the salesman ID, and only returns rows where the salesman's city is also present in the 'customer' table's list of distinct cities.

Visual Explanation:

Practice Online

Sample Database: inventory

Contribute your code and comments through Disqus.

Previous SQL Exercise: Customers with higher grades than everyone in New York.
Next SQL Exercise: Customers whose grade differs from a London customer.