Skip to main content

Having clause

In SQL, the HAVING clause is used in combination with the GROUP BY clause to filter the results of a GROUP BY operation. Similar to the WHERE clause, HAVING allows us to specify conditions that filter the results. However, while WHERE filters individual rows, HAVING filters groups.

Basic Syntax​

The basic syntax of the HAVING clause is as follows:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Usage of HAVING Clause​

The HAVING clause is most often used when there is a need to filter the results of a grouping. For instance, if you have a table of sales data and you want to find out which product categories have total sales greater than a certain amount, you'd use the HAVING clause.

Here's an example:

SELECT product_category, SUM(sale_amount)
FROM sales
GROUP BY product_category
HAVING SUM(sale_amount) > 10000;

In this query, the GROUP BY clause groups the sales data by product category. The HAVING clause then filters out those groups which have a total sale amount of less than 10,000.

Difference Between WHERE and HAVING Clause​

The key difference between WHERE and HAVING is when they are applied in the query process. The WHERE clause is applied before the data is grouped, and the HAVING clause is applied after the data is grouped. This is why the HAVING clause can reference aggregate functions and the WHERE clause cannot.

For instance, the following query will raise an error:

SELECT product_category, SUM(sale_amount)
FROM sales
WHERE SUM(sale_amount) > 10000
GROUP BY product_category;

This is because the WHERE clause cannot reference the aggregate function SUM(sale_amount).

HAVING Clause with COUNT Function​

The HAVING clause can also be used with the COUNT function to filter groups based on the count of rows in each group. Here's an example:

SELECT product_category
FROM sales
GROUP BY product_category
HAVING COUNT(*) > 10;

This query returns all product categories that have more than 10 sales entries in the sales table.

Conclusion​

In this tutorial, we've learned about the HAVING clause in SQL. We use this clause to filter groups in our results. It's similar to the WHERE clause but is used after the data is grouped. Remember that the HAVING clause can reference aggregate functions, while the WHERE clause cannot. Understanding how and when to use the HAVING clause can help make your SQL queries more efficient and effective.