WikiGalaxy

Personalize

SQL HAVING Clause

The SQL HAVING clause is used in conjunction with the GROUP BY clause to filter groups based on a specified condition. It is particularly useful when you want to apply a condition to aggregated data, such as sums or averages, rather than individual rows.

Introduction to SQL HAVING Clause

The HAVING clause in SQL is used to filter records that are returned by a GROUP BY operation. Unlike the WHERE clause, HAVING can be used to filter aggregate functions like COUNT, SUM, AVG, etc.


SELECT department, COUNT(employee_id) 
FROM employees 
GROUP BY department 
HAVING COUNT(employee_id) > 5;

Console Output:

Sales - 6

Marketing - 8

Using HAVING with SUM

You can use the HAVING clause to filter results based on the sum of a column. This is useful for finding groups where the total meets a certain criterion.


SELECT department, SUM(salary) 
FROM employees 
GROUP BY department 
HAVING SUM(salary) > 100000;

Console Output:

Engineering - 150000

Finance - 120000

Combining HAVING with WHERE

The WHERE clause filters rows before grouping, while the HAVING clause filters groups after aggregation. This combination allows for precise data retrieval.


SELECT department, AVG(salary) 
FROM employees 
WHERE position = 'Manager' 
GROUP BY department 
HAVING AVG(salary) > 70000;

Console Output:

HR - 75000

IT - 80000

HAVING with Multiple Conditions

The HAVING clause can handle multiple conditions using logical operators like AND and OR, providing flexibility in data filtering.


SELECT department, COUNT(employee_id) 
FROM employees 
GROUP BY department 
HAVING COUNT(employee_id) > 5 AND SUM(salary) > 50000;

Console Output:

Operations - 7

Logistics - 9

HAVING with COUNT

Using HAVING with COUNT is a common practice to filter groups based on the number of records in each group.


SELECT department, COUNT(employee_id) 
FROM employees 
GROUP BY department 
HAVING COUNT(employee_id) > 10;

Console Output:

Customer Service - 12

Support - 15

HAVING with AVG

The HAVING clause can be used with AVG to filter groups based on their average values, such as average salaries or scores.


SELECT department, AVG(salary) 
FROM employees 
GROUP BY department 
HAVING AVG(salary) > 60000;

Console Output:

Research - 65000

Development - 70000

HAVING with MAX

Using HAVING with MAX allows you to filter groups based on the maximum value within each group.


SELECT department, MAX(salary) 
FROM employees 
GROUP BY department 
HAVING MAX(salary) > 90000;

Console Output:

Executive - 95000

Management - 98000

HAVING with MIN

The HAVING clause can also be used with MIN to filter groups based on their minimum values, such as the lowest salary in each department.


SELECT department, MIN(salary) 
FROM employees 
GROUP BY department 
HAVING MIN(salary) > 40000;

Console Output:

Administration - 45000

Operations - 47000