w3resource

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 Expression: Find the highest purchase amount ordered by the each customer.

Relational Algebra Tree:

Relational Algebra Tree: Find the highest purchase amount ordered by the each customer.

Explanation:

Syntax of find the highest purchase amount ordered by the each customer

Visual presentation:

Find the highest purchase amount ordered by the each customer

Practice Online


Query Visualization:

Duration:

Query visualization of Find the highest purchase amount ordered by the each customer - Duration

Rows:

Query visualization of Find the highest purchase amount ordered by the each customer - Rows

Cost:

Query visualization of Find the highest purchase amount ordered by the each customer - 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.

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.

SQL: Tips of the Day

What is the best way to paginate results in SQL Server?

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

Database: SQL Server

Ref: https://bit.ly/3MGrNlk

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook