SQL Exercises: Highest purchase amount ordered by the each customer

SQL Aggregate Functions: Exercise-9 with Solution

From the following table, write a SQL query to find the highest purchase amount ordered by each customer. Return customer ID, maximum purchase amount.

Sample table: orders

Sample Solution:

SELECT customer_id,MAX(purch_amt) 
FROM orders 
GROUP BY customer_id;

Output of the Query:

customer_id	max
3007		2400.60
3008		250.45
3002		5760.00
3001		270.65
3009		2480.40
3004		1983.43
3003		75.29
3005		948.50

Code Explanation:

The given SQL statement retrieves the maximum "purch_amt" for each "customer_id" from the "orders" table.
It uses the "GROUP BY" clause to group the "orders" table by "customer_id", which means that the result of the query will show the maximum "purch_amt" for each distinct "customer_id".
The "MAX" function is used to find the maximum "purch_amt" value within each group.

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: Highest grade for each of the cities of the customers.
Next SQL Exercise: Find the highest purchase amount each customer ordered.