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 a comprehensive book on SQL Server 2014.  For any comments you can email the author at alphapress@hotmail.com.

  

 

 

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

Discussion:

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. 

Code:

SELECT LastName, SUM(unitprice*quantity) AS OrderTotal

FROM tbls_customersgr

WHERE productname <> "Chocolate Chip Brownie"

GROUP BY LastName

HAVING SUM(unitprice*quantity)>100
 

Result: