HAVING Clause | Database

Introduction

HAVING clause in SQL is used with the GROUP BY clause to filter groups based on a specified list of conditions. HAVING clause applies to summarized group records, whereas WHERE clause applies to individual records.

Syntax

Following is the syntax of HAVING clause

SELECT column_names
FROM table_name
WHERE condition
GROUP BY column-names
HAVING condition
ORDER BY column-names

In this syntax, GROUP BY clause summarizes the rows into groups and the HAVING clause applies one or more conditions to these groups. Only groups that make the conditions evaluate to TRUE are included in the result.

Example

Create a employee table (emp) and insert record into it.

/*Create Table */
CREATE TABLE emp(
  id INT,
  name VARCHAR(10),
  age INT,
  salary INT
)

/* Insert record */
INSERT INTO emp(id,name,age,salary)
VALUES(1, 'Rohan', 34, 5000),
(2, 'Scott', 43, 10000),
(3, 'Anu', 34, 8000),
(4, 'Vishal', 30, 5000),
(5, 'Shane', 25, 8000),
(6, 'Rohit', 40, 10000)

Below example count employees in each salary group having salary greater than 5000.

/* Count employees in each salary group having salary greater than 5000 */
SELECT salary, COUNT(name) AS E_Count FROM emp GROUP BY salary HAVING salary > 5000 ORDER BY salary

/* Result of Query */
salary  E_Count
8000    2
10000   2

Reference

HAVING(SQL)