AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890
Email: alphapress@hotmail.com  


  
Click to buy this book from Amazon

Section Menu 

News

The Access 2007 Pure SQL book contains 30 chapters with hundreds of solutions to frequent database problems and scenarios.  

 

Use a subquery to find above average priced products

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


Use a subquery to find above average priced products (working on one table)

Find products with prices above the average product price

Discussion:

In this example, we are looking for products with above average prices.  We want the database to calculate the average price per product, compare every product price to the average price, and display only those products which exceed the average price.  It looks like a lot for a single SQL statement, but it is possible.  The essence of this SQL statement is in the WHERE clause where we ask the ProductUnitPrice to be bigger than the average ProductUnitPrice using the inequality predicate ">" and the aggregate function avg(). 

Code:

SELECT productname, ProductUnitPrice

FROM Products

WHERE (ProductUnitPrice) > (SELECT avg(ProductUnitPrice) FROM Products)

ORDER BY ProductUnitPrice DESC

 

Result: