AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890
Email: alphapress@hotmail.com 


  

Section Menu 

Are you a University or College bookstore and want to order multiple books?  Email us at alphapress@hotmail.com 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 alphapress@hotmail.com.

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

  

 

 

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: