Filtering grouped data using the HAVING clause

In SQL, the HAVING clause is used to filter grouped data based on conditions specified in the query. While the WHERE clause filters individual rows before grouping, the HAVING clause filters the result set after grouping has been applied. It allows you to apply conditions to the grouped data and retrieve only the groups that meet the specified criteria.

Table of Contents

Basic Syntax

The basic syntax of a SELECT statement with the GROUP BY and HAVING clauses is:

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

column1, column2, …: Specifies the columns to retrieve from the table.
aggregate_function(column): Represents an aggregate function applied to a specific column.
table_name: Specifies the table from which to retrieve the data.
GROUP BY: Indicates that the result set should be grouped based on the specified columns.
HAVING: Specifies the condition to filter the grouped data.

Filtering Grouped Data

The HAVING clause allows you to apply conditions to the grouped data. It is similar to the WHERE clause but operates on the aggregated data.

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;

Using the query above, we can retrieve the department ID and average salary from the “employees” table. Then group the data by department ID, and filter the result to include only those departments with an average salary greater than 50,000.

Using Logical Operators

You can use logical operators such as AND and OR in the HAVING clause to create more complex conditions.

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000 AND COUNT(*) > 5;

Now, we are not only filtering the departments with average salary greater than 50,000 , but with AND operator we are including the departments that have more than 5 employees.

By using the HAVING clause, you can filter previously grouped data based on specific conditions, allowing you to retrieve only the groups that meet your criteria. This is particularly useful when you want to apply filters to the result set after performing aggregate calculations. The HAVING clause provides flexibility in refining and analyzing grouped data in SQL queries.