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

Pindaro is now working on the next version of SQL on Access 2013.  Expected publication date is mid January 2015.  If you are an instructor and need a preview copy please let us know at alphapress@hotmail.com.  We will be very happy to ship you or email you one.

The "SQL Server T-SQL Comprehensive version 2012" College Edition book is the latest book published from Pindaro in September 2014.  The book is available for purchace from Amazon.  

 

 

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: