SQL Query to Find the Highest Purchase Amount Ordered by the Each Customer

In order to find the highest purchase amount of each customer, we can use the GROUP BY clause which is very useful with aggregate functions. We use MAX() function with GROUP BY  to find the highest purchase of each customer.

In this article let us see SQL Query to Find the Highest Purchase Amount Ordered by Each Customer using MSSQL as a server.

Step 1: We are creating a Database. For this use the below command to create a database named GeeksforGeeks.

Query:

CREATE DATABASE GeeksforGeeks;

Step 2: To use the GeeksforGeeks database use the below command.

Query:

USE GeeksforGeeks

Output:

Step 3: Now we creating a table. Create a table customer_order_details with  4 columns using the following SQL query.

Query:

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

Output:

Step 4: The query for Inserting rows into the Table. Inserting rows into customer_order_details  table using the following SQL query.

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: Viewing the description of the table.

Query:

EXEC sp_columns customer_order_details

Output:

 Step 6: Viewing the inserted data

Query:

SELECT * FROM customer_order_details

Output:

• Query to Find the Highest Purchase Amount Ordered by the 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:

• 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: