SQL - Having Clause

The HAVING clause in SQL is used in combination with the GROUP BY clause to filter the result set based on the results of aggregate functions applied to grouped rows. Essentially, it allows you to specify a condition that must be met by a group in order for that group to be included in the result. The HAVING clause is used to filter group-level data, whereas the WHERE clause filters individual rows.

The basic syntax of the HAVING clause is as follows:

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
GROUP BY column1, column2, ...
HAVING condition;

In the above syntax, column1, column2, ... represent the columns you want to group by, aggregate_function(column) represents the aggregate function you want to apply to each group, and condition represents the filtering condition that applies to the aggregated values.

For example, let's say you have a table called "Orders" with columns such as "OrderID", "CustomerID", "ProductID" and "Quantity". And this "Orders" table have the following sample data:

Now, let's say you want to find customers who have ordered a total quantity of products greater than or equal to 10. You can use the HAVING clause in conjunction with the GROUP BY clause to achieve this. Here's the SQL query:

SELECT
    CustomerID,
    SUM(Quantity) AS TotalQuantity
FROM
    Orders
GROUP BY
    CustomerID
HAVING
    SUM(Quantity) >= 10;

In this query:

• We select the "CustomerID" column to identify each customer.
• We use the SUM function to calculate the total quantity of products ordered by each customer.
• We give an alias to the calculated column using AS TotalQuantity for clarity.
• We use the GROUP BY clause to group the rows by "CustomerID," creating one row per customer.
• We use the HAVING clause to filter the grouped results and include only those customers whose total quantity is greater than or equal to 10.

The result of this query would be:

In this result, you can see that only customer 101 has a total quantity of products ordered greater than or equal to 10, as per the HAVING clause condition.

The HAVING clause is similar to the WHERE clause, but it operates on the aggregated values of the grouped data rather than individual rows. It allows you to filter the groups based on conditions that cannot be easily expressed using the WHERE clause.

It's important to note that the HAVING clause is applied after the GROUP BY clause, so you can use aggregate functions in the HAVING clause to refer to the result of the aggregation in the SELECT clause.

Additionally, just like the WHERE clause, you can use logical operators such as AND, OR, and NOT in the HAVING clause to create complex conditions.

Remember that the HAVING clause can only be used in combination with the GROUP BY clause. If you're not using the GROUP BY clause in your query, you should use the WHERE clause instead to filter individual rows.