Overview of HAVING with SQL

Overview of HAVING

The HAVING clause is used to filter the results of a query after the GROUP BY clause has been applied. Unlike the WHERE clause, which filters rows before aggregation, HAVING allows you to filter groups created by GROUP BY based on aggregate function results.

Syntax

The general syntax for the HAVING clause is: 

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

• column1, column2, …: Columns by which the data is grouped.
• aggregate_function(column3): Aggregate function applied to the grouped data.
• condition: Condition to apply to the groups after aggregation.

Examples

Example 1: Filtering Groups with HAVING

Suppose you have a sales table with columns product_id, sales_amount, and sales_date. To get the products with a total sales amount greater than 10,000: 

SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_id
HAVING SUM(sales_amount) > 10000;

 Explanation:

• GROUP BY product_id groups the sales data by product.
• SUM(sales_amount) calculates the total sales for each product.
• HAVING SUM(sales_amount) > 10000 filters the groups to include only those where the total sales exceed 10,000.

Example 2: Excluding Groups Based on Aggregated Condition

To get departments with more than 5 employees: 

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Explanation:

• GROUP BY department groups the employees by department.
• COUNT(*) counts the number of employees in each department.
• HAVING COUNT(*) > 5 filters out departments with 5 or fewer employees.

Example 3: Filtering with a Complex Aggregated Condition

To find departments where the average salary is below 50,000 and the number of employees is greater than 10: 

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) < 50000 AND COUNT(*) > 10;

Explanation:

• GROUP BY department groups the employees by department.
• AVG(salary) calculates the average salary in each department.
• COUNT(*) counts the number of employees in each department.
• HAVING AVG(salary) < 50000 AND COUNT(*) > 10 filters to include only those departments where the average salary is less than 50,000 and there are more than 10 employees.

Important Points

• Order of Execution: The HAVING clause is applied after the aggregation performed by GROUP BY. The WHERE clause, on the other hand, is applied before aggregation.
• Aggregate Functions: HAVING is used to filter based on aggregate function results (such as SUM, COUNT, AVG, MAX, MIN).
• Combining with WHERE: You can use both WHERE and HAVING in the same query. WHERE filters rows before aggregation, while HAVING filters groups after aggregation.

SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department
HAVING COUNT(*) > 5;

Explanation:

• WHERE hire_date > ‘2020-01-01’ filters employees hired after January 1, 2020.
• GROUP BY department groups the filtered employees by department.
• HAVING COUNT(*) > 5 filters to include only departments with more than 5 employees among those hired after the specified date.

Conclusion

The HAVING clause is essential for filtering groups of data after aggregation has been performed. It allows you to apply conditions on aggregated results, providing more flexibility in report generation and data analysis. By using HAVING in combination with GROUP BY, you can extract meaningful insights based on complex conditions.