Builder for Microsoft Access
Need to see key data from your Access database in a single dashboard? Tired of creating query after query to really understand your Access data? Dashboard Builder for Microsoft Access is an easy to use tool to create powerful dashboards in minutes…without being a SQL wizard. Free trial available
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.
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: