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


  

Section Menu 

Are you a University or College bookstore and want to order multiple books?  Email us at alphapress@hotmail.com or call us at 518-250-3890.  We will be happy to talk to you.  

Are you a professor and you need a sample copy?  We will be happy to send you a pdf or printed copy!  Email us at alphapress@hotmail.com.

Are you outside the United States?  We can ship multiple books worldwide in most countries.

  

 

 

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: