AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890

Click to buy this book from Amazon

Section Menu 


Pindaro is now working on a comprehensive book on SQL Server 2014.  For any comments you can email the author at




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


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(). 


SELECT productname, ProductUnitPrice

FROM Products

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

ORDER BY ProductUnitPrice DESC