SQL Exercises: Customer with the highest purchase amount
From the following table, write a SQL query to determine the maximum order (purchase) amount generated by each salesperson. Filter the rows for the salesperson ID is in the range 5003 and 5008 (Begin and end values are included.). Return salesperson id and maximum purchase amount.
Sample table: orders
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
Sample Solution:
-- This query retrieves the maximum 'purch_amt' value for each 'salesman_id' from the 'orders' table.
-- The result set is then filtered to only include rows where the 'salesman_id' is between 5003 and 5008.
SELECT salesman_id, MAX(purch_amt)
-- Specifies the table from which to retrieve the data (in this case, 'orders').
FROM orders
-- Groups the result set by the 'salesman_id' column.
GROUP BY salesman_id
-- Filters the grouped results to only include rows where the 'salesman_id' is between 5003 and 5008.
HAVING salesman_id BETWEEN 5003 AND 5008;
Output of the Query:
salesman_id max 5005 270.65 5003 2480.40 5007 75.29 5006 1983.43
Code Explanation:
In the given SQL query, the salesman_id and the maximum purchase amount (purch_amt) will be retrieved from the 'orders' table for salesmen with an id between 5003 and 5008.
The result will show the maximum purchase amount made by each salesman in the specified range. The "HAVING" clause filters the result set by only showing salesmen whose id is between 5003 and 5008.
The result will show the maximum purchase amount made by each salesman, with the salesman_id being grouped together.
Explanation:
Visual presentation:
Practice Online
For more Practice: Solve these Related Problems:
- Write a SQL query to determine the maximum order amount generated by each salesperson with IDs in the range 5001 to 5005.
- Write a SQL query to determine the maximum order amount generated by each salesperson with IDs in the range 5002 to 5006, but only for orders placed in 2012.
- Write a SQL query to determine the maximum order amount generated by each salesperson with IDs in the range 5003 to 5007, but only for orders with a purchase amount greater than 1000.
- Write a SQL query to determine the maximum order amount generated by each salesperson with IDs in the range 5004 to 5008, but only for salespeople from the city 'Paris'.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Customer details and purchases over a certain amount.
Next SQL Exercise: Count all orders for a specific date.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.