SQL HAVING

What is the SQL HAVING Clause?

The SQL HAVING clause is used to filter the results of a GROUP BY query based on the result of an aggregate function. It is similar to the WHERE clause but is specifically applied after grouping and aggregation, allowing you to filter on the results of aggregate functions like COUNT, SUM, AVG, and others.

When you would use it

You would use the HAVING clause when you need to filter grouped data based on the result of aggregate functions. It is especially useful when you want to set conditions on the groups themselves, such as filtering groups with a certain count, sum, or average value.

Syntax

The syntax for the HAVING clause is as follows:

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

• column1, column2: Columns you want to select and group by.
• aggregate_function(column3): The aggregate function (e.g., SUM, COUNT, AVG) applied to column3.
• table_name: The name of the table.
• condition: The condition used to filter grouped results based on the aggregate function.

Parameter values

• column1, column2: Columns from the table.
• aggregate_function(column3): An aggregate function applied to a specific column.
• table_name: The name of the table.
• condition: A condition that filters the grouped results based on the result of an aggregate function.

Example query

Let's assume we have a table named "orders" with columns "product_id," "quantity," and "order_date." We want to find products that have a total quantity ordered greater than 100:

SELECT product_id, SUM(quantity) as total_quantity
FROM orders
GROUP BY product_id
HAVING SUM(quantity) > 100;

In the above query, we use the HAVING clause to filter products with a total quantity ordered greater than 100.

Example table response

Suppose the "orders" table contains the following data:

| product_id | quantity | order_date  |
|------------|----------|------------ |
| 1         | 50       | 2023-01-01  |
| 2         | 75       | 2023-01-02  |
| 1         | 60       | 2023-01-03  |
| 3         | 40       | 2023-01-04  |

The query mentioned earlier would return the following result:

| product_id | total_quantity |
|------------|----------------|
| 1         | 110            |

This shows the products (product_id) with a total quantity ordered greater than 100.

Use cases

• Filtering groups based on the result of aggregate functions.
• Finding products, customers, or categories with specific characteristics in aggregated data.
• Applying conditions to groups created by the GROUP BY clause.

SQL languages this is available for

The HAVING clause is a standard SQL feature and is available in most relational database management systems (RDBMS) that support SQL, including MySQL, PostgreSQL, Oracle, SQL Server, and SQLite. The specific syntax and behavior may vary slightly between database systems, but the fundamental functionality remains the same.