SQL Server HAVING Clause Explained with Practical Examples

Last Updated: 28th October 2025

Syntax HAVING

SELECT Col1, FUNCTION(Col2)
    FROM dbo.table_name
    GROUP BY Col1
    HAVING CONDITION

📘 Example Setup: The dbo.Books Table

To demonstrate how the HAVING clause works in SQL Server, we'll use a sample table called dbo.Books. You can create this table in your SQL Server database, or use any other table that includes numeric columns. These are essential for applying aggregate functions like "SUM()", COUNT(), or AVG().

Using HAVING clause in SQL Server

Take another look at the table above. It contains books from various categories, such as Computer, Science, and others, each with a price tag. To calculate the total price for each category, you can use the SUM() function, which is one of SQL Server's aggregate functions. Here's how you can do it:

SELECT Category, SUM(Price) 'Total Price'
    FROM dbo.Books
    GROUP BY Category

It shows the sum of the price, grouped by the Category (or for each group).

Note: When using an aggregate function like SUM() alongside a regular column such as Category, you must include a GROUP BY clause in your SQL query. Without it, SQL Server will return an error because it doesn't know how to group the non-aggregated column.

Now, let's take it a step further. Suppose you want to filter the grouped results and only display categories where the total price exceeds 150. This is where the HAVING clause comes in. It allows you to apply conditions after aggregation.

SELECT Category, SUM(Price) 'Total Price' 
    FROM dbo.Books
    GROUP BY Category
    HAVING SUM(Price) > 150

Output:

Here's another HAVING clause example and this time I am using the COUNT() function. Remember, you can use the HAVING clause with any aggregate function.

SELECT COUNT(BookID) C, Category
    FROM dbo.Books
    GROUP BY Category
    HAVING COUNT(Category) >= 2

Output:

The query returns Categories, which have 2 or more rows in the table.

Explanation:

COUNT(BookID): counts the number of books in each category.
GROUP BY Category: groups the results by each book category.
HAVING COUNT(BookID) >= 2: filters out categories with fewer than 2 books.

Using HAVING with WHERE clause Together

This is another important and commonly used SQL pattern, combining the HAVING clause with the WHERE clause.

While both WHERE and HAVING are used to filter data, they serve different purposes. See this query

SELECT Category, SUM(Price) AS [Total Price]
  FROM dbo.Books
  WHERE Category IN ('Computers', 'Science', 'Programming')
  GROUP BY Category
  HAVING SUM(Price) > 150;

Explanation:

WHERE filters the rows before aggregation, selecting only books in the specified categories.
GROUP BY Category groups all books by category.
SUM(Price) calculates the total price for each category.
HAVING SUM(Price) > 150 filters out categories whose total price is 150 or less.

Using the HAVING Clause with Multiple Conditions

You can also use the HAVING clause to apply multiple conditions, not just a single filter. For example, you might want to filter grouped results based on a range of values, such as categories where the total price is greater than 50 but less than 200.

SELECT Category, SUM(Price) AS [Total Price]
    FROM dbo.Books
    WHERE Category IN ('Computers', 'Science', 'Programming')
    GROUP BY Category
    HAVING SUM(Price) > 50 AND SUM(Price) < 200

The query filters the grouped results using HAVING to include only categories where the total price is between 51 and 199.

Using HAVING with Multiple Conditions and Pattern Matching

You can also combine multiple conditions in the HAVING clause, including pattern matching with LIKE. This allows you to filter grouped results based on both aggregate values and specific text patterns.

SELECT Category, SUM(Price) AS [Total Price]
    FROM dbo.Books
    GROUP BY Category
    HAVING Category LIKE '%comp%' and SUM(Price) < 200

← PreviousNext →