SQL HAVING Clause

Introduction

In this chapter, we will learn how to use the HAVING clause in SQL. The HAVING clause is used to filter groups of rows created by the GROUP BY clause based on specified conditions. This chapter will cover the syntax, usage, and provide examples to help you understand how to use the HAVING clause effectively.

What is the HAVING Clause?

The HAVING clause is similar to the WHERE clause, but it is used to filter groups rather than individual rows. It is often used with aggregate functions like COUNT, SUM, AVG, MAX, and MIN to filter the results of a GROUP BY query.

Syntax for HAVING

Basic Syntax

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

• column1, column2, ...: The columns by which you want to group the result set.
• aggregate_function(column2): The aggregate function to apply to the grouped data.
• table_name: The name of the table from which you want to retrieve data.
• condition: The condition that must be met for a group to be included in the result.

Example

Assume we have a table named employees:

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 1;

This command groups the rows by department_id and returns only those groups that have more than one employee.

Step-by-Step Example

1. Create a Sample Table

First, we will create a sample tables - employees and departments.

CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);

CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
department_id INT,
salary DECIMAL(10, 2)
);

2. Insert Sample Data

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');


INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ramesh', 'Kumar', 'ramesh.kumar2@example.com', 3, 50000);

3. Using HAVING with GROUP BY

To group employees by department_id and return only those groups with more than one employee:

SELECT department_id, COUNT(*) AS num_employees
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 1;

Output

department_id	num_employees
1	2
2	2

4. Using HAVING with Aggregate Functions

You can use aggregate functions in the HAVING clause to filter groups based on calculations.

Examples

• SUM: To find departments with a total salary greater than 100000:

  SELECT department_id, SUM(salary) AS total_salary
  FROM employees
  GROUP BY department_id
  HAVING SUM(salary) > 100000;
  

  Output

  department_id	total_salary
  1	105000.00
  2	122000.00

• AVG: To find departments with an average salary greater than 55000:

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

  Output

  department_id	avg_salary
  2	61000.00

• MAX: To find departments where the maximum salary is greater than 55000:

  SELECT department_id, MAX(salary) AS highest_salary
  FROM employees
  GROUP BY department_id
  HAVING MAX(salary) > 55000;
  

  Output

  department_id	highest_salary
  2	62000.00

• MIN: To find departments where the minimum salary is greater than 50000:

  SELECT department_id, MIN(salary) AS lowest_salary
  FROM employees
  GROUP BY department_id
  HAVING MIN(salary) > 50000;
  

  Output

  department_id	lowest_salary
  2	60000.00

Using HAVING with Multiple Conditions

You can combine multiple conditions in the HAVING clause using logical operators like AND and OR.

Example

To find departments with more than one employee and a total salary greater than 100000:

SELECT department_id, COUNT(*) AS num_employees, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 1 AND SUM(salary) > 100000;

Output

department_id	num_employees	total_salary
1	2	105000.00
2	2	122000.00

Conclusion

The HAVING clause is used for filtering groups of rows created by the GROUP BY clause based on specified conditions. This chapter covered the basic syntax, using HAVING with aggregate functions, and combining multiple conditions in the HAVING clause. Understanding how to use the HAVING clause effectively will enhance your ability to perform complex queries and data analysis.