w3resource
SQL exercises

SQL Exercises: Find largest number of orders booked by the customer

SQL Formatting Output : Exercise-9 with Solution

Write a SQL statement to make a report with customer ID in such a manner that, the largest number of orders booked by the customer will come first along with their highest purchase amount.

Sample table: orders


Sample Solution:

SELECT customer_id, COUNT(DISTINCT ord_no), 
MAX(purch_amt) 
FROM orders 
GROUP BY customer_id 
ORDER BY 2 DESC;

Output of the Query:

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

Explanation:

make a report with customer ID in such a manner that, the largest number of orders booked by the customer will comes first along with their highest purchase amount

Pictorial presentation:

make a report with customer ID in such a manner that, the largest number of orders booked by the customer will comes first along with their highest purchase amount

Query Visualization:

Duration:

Query visualization of Find largest number of orders booked by the customer - Duration

Rows:

Query visualization of Find largest number of orders booked by the customer - Rows

Cost:

Query visualization of Find largest number of orders booked by the customer - Cost

 

Practice Online


Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a SQL statement to display customer name, city and grade in such a manner that, the customer holding highest grade will come first.
Next: Write a SQL statement make a report with order date in such a manner that, the latest order date will come last along with the total purchase amount and total commission (15% for all salesmen) for that date.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming