SQL Exercise: Salesman details by smallest ID along with order date

SQL Formatting Output: Exercise-7 with Solution

From the following table, write a SQL query that calculates the maximum purchase amount generated by each salesperson for each order date. Sort the result-set by salesperson id and order date in ascending order. Return salesperson id, order date and maximum purchase amount.

Sample table: orders

Sample Solution :

SELECT salesman_id,ord_date,MAX(purch_amt) 
FROM orders 
GROUP BY salesman_id,ord_date 
ORDER BY salesman_id,ord_date;

Output of the Query:

salesman_id	ord_date	max
5001		2012-04-25	3045.60
5001		2012-07-27	2400.60
5001		2012-09-10	5760.00
5001		2012-10-05	65.26
5002		2012-06-27	250.45
5002		2012-09-10	948.50
5002		2012-10-05	150.50
5003		2012-08-17	110.50
5003		2012-10-10	2480.40
5005		2012-09-10	270.65
5006		2012-10-10	1983.43
5007		2012-08-17	75.29

Code Explanation:

The said query in SQL retrieves data from the 'orders' table and returns the following columns:
salesman_id
ord_date
The maximum value of the purch_amt column, calculated using the MAX function
A result of this query is grouped based on both the "salesman_id" and the "order_date" columns, as well as being sorted in ascending order based on the "salesman_id" and the "order_date" columns. The result of this query will give the maximum purchase amount for each combination of salesman_id and ord_date.

Relational Algebra Expression:

Relational Algebra Tree:

Explanation :

Visual presentation :

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Display customer name, city, grade.
Next SQL Exercise: Display customer name, city and grade by highest grade.