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

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 selects specific columns ('salesman_id', 'ord_date', and the maximum 'purch_amt') from the 'orders' table.
-- It groups the result set by 'salesman_id' and 'ord_date' and orders it in ascending order first by 'salesman_id' and then by 'ord_date'.
SELECT salesman_id, ord_date, MAX(purch_amt)
-- Specifies the table from which to retrieve the data (in this case, 'orders').
FROM orders
-- Groups the result set by 'salesman_id' and 'ord_date' columns.
GROUP BY salesman_id, ord_date
-- Orders the result set in ascending order first by 'salesman_id' and then by '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.