SQL Exercises: Highest purchase amount on a date for each salesman
11. Highest Purchase Amount by Salesperson on '2012-08-17'
From the following table, write a SQL query to determine the highest purchase amount made by each salesperson on '2012-08-17'. Return salesperson ID, 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 where the 'ord_date' is '2012-08-17'.
SELECT salesman_id, MAX(purch_amt)
-- Specifies the table from which to retrieve the data (in this case, 'orders').
FROM orders
-- Filters the rows to only include those where the 'ord_date' is '2012-08-17'.
WHERE ord_date = '2012-08-17'
-- Groups the result set by the 'salesman_id' column.
GROUP BY salesman_id;
Output of the Query:
salesman_id max 5003 110.50 5007 75.29
Code Explanation:
The said SQL query retrieves the salesman id and the maximum purchase amount for orders that have an order date of '2012-08-17' in the 'orders' table. The result will be grouped by salesman id, and only the maximum purchase amount will be returned for each group.
Relational Algebra Expression:
Relational Algebra Tree:
Explanation:

Visual presentation :

Go to:
PREV : Highest Purchase by Customer on Date.
NEXT : Highest Purchase Amount by Customer on Specific Date.
Practice Online
For more Practice: Solve these Related Problems:
- Write a SQL query to find the highest purchase amount for each salesperson on '2012-09-10'.
- Write a SQL query to find the highest purchase amount for each salesperson on '2012-10-05', but only for salespeople with a commission rate of 0.13 or higher.
- Write a SQL query to find the highest purchase amount for each salesperson on '2012-08-17', but only for salespeople from the city 'Paris'.
- Write a SQL query to find the highest purchase amount for each salesperson on '2012-07-27', but only for salespeople who have made at least two sales.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.