SQL Subquery Exercises: Find all orders with order amounts which are on or above-average amounts for their customers
SQL SUBQUERY : Exercise-13 with Solution
13. Write a queries to find all orders with order amounts which are on or above-average amounts for their customers.
Sample table: Orders
Sample table: Customer
SELECT * FROM orders a WHERE purch_amt >= (SELECT AVG(purch_amt) FROM orders b WHERE b.customer_id = a.customer_id);
Output of the Query:
ord_no purch_amt ord_date customer_id salesman_id 70009 270.65 2012-09-10 3001 5005 70005 2400.60 2012-07-27 3007 5001 70008 5760.00 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.40 2012-10-10 3009 5003 70011 75.29 2012-08-17 3003 5007 70013 3045.60 2012-04-25 3002 5001 70007 948.50 2012-09-10 3005 5002 70012 250.45 2012-06-27 3008 5002
Contribute your code and comments through Disqus.
Previous: Write a query to find all orders with order amounts which are above-average amounts for their customers.
Next: Write a query to find the sums of the amounts from the orders table, grouped by date, eliminating all those dates where the sum was not at least 1000.00 above the maximum amount for that date.
What is the difficulty level of this exercise?
- New Content published on w3resource :
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework