SQL HAVING: Filtering Summary Results in SQL

Use SQL HAVING to filter summary results from a GROUP BY.  It specifies the search condition for the group or aggregate.

SQL HAVING is only used with SELECT.  It is mostly used when a GROUP BY is present, if one isn’t there is an implicit single aggregated group.

For this example we’ll use the CustomerOrderDetail view.

If you wish, you can get the script used to build the PizzaDB here.

Here we are looking customer’s how total purchase amount is greater than $200

/* Answer */
select CustomerName,
    count(PurchaseAmount) NumberOfPurchases,
    sum(PurchaseAmount) TotalPurchases
from CustomerOrderDetail
group by CustomerName
having sum(PurchaseAmount) > 200

When constructing a SELECT statement using HAVING the order is:

SELECT Column1, Column2, ... AggregateFunction(Column3)
FROM TableName
WHERE condition
GROUP BY Column1
HAVING condition
ORDER BY Column1

In this example

• Column1, Column2, … represent the columns you which to group the result set. These columns must be part of the GROUP BY clause.
• AggregateFunction – A function like COUNT(), SUM(), or AVG() which performs a calculation on a set of values.
• TableName is the table you are querying
• WHERE condition is used to filter rows in the result set
• GROUP BY specifies the columns you which to group by.
• HAVING condition – filters grouped values
• ORDER BY allows you to sort the grouped fields.

So what do we mean that SQL HAVING filters summary results?  Let’s look at an example:

HAVING Example using COUNT

Suppose we only want to include customers having more than 20 orders.

Here you can see order counts by customer:

By using SQL HAVING we can “filter” out counts less that 20. Here is some SQL you can use to get all the customer having 20 or more purchases.

/* Answer */
select CustomerName,
    count(*) NumberOfPurchases
from CustomerOrderDetail
group by CustomerName
having count(*) >= 20

Once you run this query, you’ll see only customer having more than 19 purchases.

Also notice in this example noticed I used COUNT(*). This is a sort of shortcut, and means we’re counting every row.

Now that you’re seeing how to use HAVING, let’s see how you can combine conditions.

If you want to further narrow down your customer list you can do who using multiple conditions. We first learned about SQL Conditional Operators in an earlier session.

Let’s find all customers who purchased more than $200 and had more than 19 purchases.

/* Answer */
select CustomerName,
    count(PurchaseAmount) NumberOfPurchases,
    sum(PurchaseAmount) TotalPurchases
from CustomerOrderDetail
group by CustomerName
having count(*) > 19 and sum(PurchaseAmount) > 200

In this example we use the AND to find groups that satisfy both conditions.

Now you try! Can you list all customer from CustomerOrderDetail whose average order is greater than or equal to $10?

In your result, display the count, total orders, and average order amount for each customer.

/* Answer */
select CustomerName,
    count(PurchaseAmount) NumberOfPurchases,
    sum(PurchaseAmount) TotalPurchases,
    avg(PurchaseAmount) AveragePurchase
from CustomerOrderDetail
group by CustomerName
having avg(PurchaseAmount) >= 10