SQL Subquery Exercises: write a query in sql to find the name, city, and the total sum of orders amount a salesman collects. Salesman should belong to the cities where any of the customer belongs.

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.

Sample table: Orders

Sample table: Customer

Sample Solution:

SELECT salesman.name, salesman.city, subquery1.total_amt FROM 
salesman, (SELECT salesman_id, SUM(orders.purch_amt) AS total_amt 
FROM orders GROUP BY salesman_id) subquery1 WHERE subquery1.salesman_id = salesman.salesman_id 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)

Practice Online

Contribute your code and comments through Disqus.

Previous: From the following tables, write a SQL query to find those customers whose grade are higher than customers living in New York City. Return customer_id, cust_name, city, grade and salesman_id.
Next: From the following tables, write a SQL query to find those customers whose grade doesn't same of those customers live in London City. Return customer_id, cust_name, city, grade and salesman_id.