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


Section Menu 

Are you a University or College bookstore and want to order multiple books?  Email us at or call us at 518-250-3890.  We will be happy to talk to you.  

Are you a professor and you need a sample copy?  We will be happy to send you a pdf or printed copy!  Email us at

Are you outside the United States?  We can ship multiple books worldwide in most countries.




The GROUP BY clause with WHERE and HAVING

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


 The GROUP BY clause on one column, one aggregated field, WHERE and HAVING

Calculate order totals above $100 excluding a subset of products


In this example, pay attention to the combined use of WHERE and HAVING.  We want to calculate customer order totals, but we want to exclude from the result set customers whose order totals are less than $100.  In addition, we want to exclude the calculation amounts related to the product "Chocolate Chip Brownie".  Perhaps management wants to see how customer order amounts differ if this product is excluded from their orders.  Maybe they are thinking of discontinuing this particular product. 

In general, when it comes to filtering records in GROUP BY statements, we need to make some quick decisions:  Do we need to use WHERE, HAVING, or a combination of the two?  Your way of thinking should always be the same:  Use the WHERE clause to exclude records that you do not want to be included in the aggregate calculations, and use HAVING to exclude values after the aggregations by GROUP BY are made.  In this case, we use WHERE to exclude the product "Chocolate Chip Brownie" from the recordset, and we use GROUP BY to create the aggregations on whatever records remain.  After the aggregations are made, the HAVING clause takes effect to exclude order totals less than $100.  The bottom line is that in the SQL statement below, the WHERE clause will run first, then the GROUP BY, and finally the HAVING clause. 


SELECT LastName, SUM(unitprice*quantity) AS OrderTotal

FROM tbls_customersgr

WHERE productname <> "Chocolate Chip Brownie"


HAVING SUM(unitprice*quantity)>100