SQL Having Clause

Let's say you have a query that you want to run, where you summarize customer information (order totals, number of calls, etc.).  With your query, though, you only want to include customers that have over a certain value for what you are summarizing.  The SQL WHERE clause doesn't allow for aggregate functions, so what do you do?

To exclude customers under a certain value, you would have to use the SQL HAVING Clause.  There are a few things you need to keep in mind when using the HAVING Clause.

• The HAVING Clause occurs after your GROUP BY.
• To use the HAVING Clause, you must have a GROUP BY because you are aggregating data together
• You do not need a WHERE Clause

So, how does the HAVING Clause work?  Let's say that we want to see the total dollar value of a customer's order.  The statement would look something like this:

SELECT LastName, SUM(Total) Total

from OrderDetails

Group By LastName

Now, we want the query to return customers whose sum total is greater than $100.  That query would look like this:

SELECT LastName, SUM(Total) Total

from OrderDetails

Group By LastName

Having SUM(Total) > 100

We can also throw a WHERE Clause in there if we just want it returned for customers that have a certain last name:

SELECT LastName, SUM(Total) Total

from OrderDetails

WHERE LastName = 'Smith'

Group By LastName

Having SUM(Total) > 100

The HAVING Clause can also be used for non-aggregate functions, but at that point, it is better to use the WHERE Clause.  This is because the WHERE Clause removes the data before the result set is returned, while the HAVING Clause is applied to the rows after the result set has been returned.

Read more on SQL HAVING Clause here: SQL Having Clause