SQL Exercises 4 — Aggregate Functions

1. Calculate total purchase amount of all orders. Return total purchase amount.

2. Calculate average purchase amount of all orders. Return average purchase amount.

3. Count the number of unique salespeople. Return number of salespeople.

4. Find the maximum purchase amount.

5. Find the minimum purchase amount.

6. Find the highest purchase amount ordered by each customer. Return customer ID, maximum purchase amount.

7. Find the highest purchase amount ordered by each customer on a particular date. Return, order date and highest purchase amount.

8. Find the highest purchase amount on ‘2012–08–17’ by each salesperson. Return salesperson ID, purchase amount.

9. Find highest order (purchase) amount by each customer in a particular order date. Filter the result by highest order (purchase) amount above 2000.00. Return customer id, order date and maximum purchase amount.

10. Find the maximum order (purchase) amount in the range 2000, 6000 (Begin and end values are included.) by combination of each customer and order date. Return customer id, order date and maximum purchase amount.

11. Find the maximum order (purchase) amount by the combination of each customer and order date. Filter the rows for maximum order (purchase) amount is either 2000, 3000, 5760, 6000. Return customer id, order date and maximum purchase amount.

12. Find the maximum order (purchase) amount by each customer. The customer ID should be in the range 3002 and 3007(Begin and end values are included.). Return customer id and maximum purchase amount.

13. Find the maximum order (purchase) amount for each customer. The customer ID should be in the range 3002 and 3007(Begin and end values are included.). Filter the rows for maximum order (purchase) amount is higher than 1000. Return customer id and maximum purchase amount.

14. Find the maximum order (purchase) amount generated by each salesperson. Filter the rows for the salesperson ID is in the range 5003 and 5008 (Begin and end values are included.). Return salesperson id and maximum purchase amount.

15. Count all the orders generated on ‘2012–08–17’. Return number of orders.

16. Count number of orders by the combination of each order date and salesperson. Return order date, salesperson id.

1. Count the number of customers. Return number of customers.

2. Find the number of customers who got at least a gradation for his/her activity.

3. Find the highest grade of the customers for each of the city. Return city, maximum grade.

1. Count number of salespeople who belongs to a city. Return number of salespeople.

1. Calculate the average product price. Return average product price.

2. Count number of products where product price is higher than or equal to 350. Return number of products.

3. Compute the average price for unique companies. Return average price and company id.

1. Compute the sum of the allotment amount of all departments. Return sum of the allotment amount.

1. Find the number of employees in each department. Return department code and number of employees.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store