databasedev.co.uk - database solutions and downloads for microsoft access

SQL - Using GROUP BY with HAVING:

If you need to restrict the number of rows returned in an SQL query in which you are using an aggregate function and a GROUP BY clause, you can use the HAVING clause instead of the WHERE clause.

The HAVING clause offers the distinct advantage over the WHERE clause as it enables you to use aggregate functions to restrict the rows returned in the results.

SQL HAVING Example:

Consider that we are using the following table relating to MovieTitles:

As an example, we could use the following SQL query to display the MovieRating and Average RentalPrice of all movies for each MovieRating as long as the average RentalPrice of the movies is greater than or equal to £2.75:

SELECT strMovieRating, Avg(curRentalPrice) AS AvgOfcurRentalPrice
FROM tblMovies
GROUP BY strMovieRating
HAVING AVG(curRentalPrice) >= 2.75

This would return the following results:

One other difference between a WHERE clause and a HAVING clause is that the WHERE clause restricts the groups of rows on which the aggregate function calculates its results; in contrast, the aggregate function calculates values for all groups of rows but only displays those that meet the HAVING clause's criteria in the results set.

Check out the following articles for more SQL topics:

• SQL WHERE clause
• SQL Aggregate Functions
• SQL GROUP BY clause