Find the Highest Purchase Amount Ordered by Each Customer

Let’s look at an example of how to find the highest purchase amount ordered by each customer

Step 1: Create a database called “w3wiki”.

Query:

CREATE DATABASE w3wiki;

Step 2: Use the w3wiki database.

Query:

USE w3wiki

Output:

Step 3: Create a table customer_order_details with 4 columns.

Query:

CREATE TABLE customer_order_details(
customer_id VARCHAR(20),
customer_name VARCHAR(20),
order_id VARCHAR(20),
order_price INT)

Output:

Step 4: Insert rows into the Table.

Query:

INSERT INTO customer_order_details
VALUES( 'C1098','PRADEEP','O3006', 20000),
( 'C1098','PRADEEP','O3010', 5000),
( 'C1098','PRADEEP','O3016', 600),
( 'C1100','KIRAN','O3068', 1000),
( 'C1100','KIRAN','O3075', 200),
( 'C1195','PRANAV','O3072', 6000),
( 'C1195','PRANAV','O3045', 80000),
( 'C2026','BUTCHI RAJU','O3056', 100000),
( 'C2026','BUTCHI RAJU','O3058', 20000)

Output:

Step 5: View the inserted data

Query:

SELECT * FROM customer_order_details

Output:

Query to Find the Highest Purchase Amount Ordered by Each Customer

Now let’s look at the SQL query to find the highest purchase of each customer.

Query:

SELECT customer_id , MAX(order_price) AS HighestPurchase
FROM customer_order_details
GROUP BY customer_id 
ORDER BY MAX(order_price) DESC

Output:

SQL query to find the number of orders ordered by each customer

Let’s look at the SQL query to find the number of orders ordered by each customer

Query:

SELECT customer_id , COUNT(order_id) AS NoofOrders
FROM customer_order_details
GROUP BY customer_id

Output:

To find the highest purchase amount for each customer, use the SQL MAX() function with GROUP BY to find the highest purchase of each customer.

Here, we will see the SQL query to find the highest purchase amount ordered by each customer.