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.

  

 

 

SELECT with WHERE, GROUP BY, HAVING and ORDER BY

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


Calculate the number of customers in all states except NY, show states with more than ten customers, and sort by biggest number of customers

Discussion:

Our business manager asked us to calculate the number of customers in each state.  In addition, she asked to exclude New York State from the results.  From the remaining states, she also asked to exclude states with less than ten customers.  Finally, states with bigger numbers of customers should appear first in the result set.   

To comply with the above requirements we need to use SELECT, WHERE, GROUP BY, HAVING, and ORDER BY in combination.  Let us examine the purpose of each statement:  First, we use the SELECT statement to select two fields from the customers table. Specifically, we select the state field as it is and the CustomerID field on which we apply the Count() function to calculate the number of occurrences of CustomerID in the table. Since CustomerID is the primary key of the table, we know that Count() will produce reliable results because there is no way to have null values in a primary key field.   

Then, we use the WHERE clause with the inequality operator "<>" to exclude from the calculations customers in New York State.  Practically, "<>" means retrieve everything else except 'NY'.  Next, we use the GROUP BY clause to aggregate calculations by state.  That is, the database will calculate the number of customers and produce results by state since we group on that field.   

The next point is a bit tricky if not understood well.  The WHERE and HAVING clauses are both filtering statements.  That is, we use them both to obtain a subset of records.  They do however have their specific roles in SQL statements and they can be used individually or in combination.  In this specific example, we use the WHERE clause to exclude from the result set customers from the state of New York.  The WHERE clause will run before any groupings and calculations by the GROUP BY clause.  In this particular example, the WHERE clause will exclude customers from NY, and then the GROUP BY clause will produce groups and count customers for the remaining records.  After the groups of customers by state are generated from the GROUP BY clause and the customer numbers are calculated by the count() function, the HAVING clause takes effect.  It will exclude from the final result any states with less than ten customers. That is, the HAVING clause will wait up until the groupings and calculations are complete before it takes effect.  This is logical since the numbers produced by the count() function are not known in advance. 

Finally, the ORDER BY clause takes effect and it will sort results by the highest number of customers.  What you need to remember in one sentence is that when using the WHERE and HAVING clauses in combination the WHERE clause always takes effect first and the HAVING clause will take effect after the groups by the GROUP BY clause have been established.  The ORDER BY clause will take effect last.     

Code:

SELECT State, Count(CustomerID) As NumberOfCustomers

FROM Customers

WHERE State <> "NY"

GROUP BY State

HAVING Count(CustomerID) >10

ORDER BY Count(CustomerID) DESC
 

Result: