w3resource

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 Expression: Highest purchase amount on a particular date for individual salesman.

Relational Algebra Tree:

Relational Algebra Tree: Highest purchase amount on a particular date for individual salesman.

Explanation:

Syntax of Highest purchase amount on a particular date for individual salesman

Visual presentation :

Highest purchase amount on a particular date for individual salesman

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.



Follow us on Facebook and Twitter for latest update.