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
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
WHERE (ProductUnitPrice) > (SELECT avg(ProductUnitPrice) FROM Products)
ORDER BY ProductUnitPrice DESC