SQL Reference - HAVING

HAVING

HAVING is a clause in SQL used in combination with the GROUP BY clause to filter out rows after they have been grouped. It sets conditions for the grouping of rows, allowing the user to eliminate groups based on certain criteria. Unlike the WHERE clause, which filters rows before they are grouped, HAVING filters the results after the grouping is performed.

Example

SELECT Department, COUNT(Employee) AS Number_Of_Employees
FROM Company
GROUP BY Department
HAVING COUNT(Employee) > 3;

Output

+--------------+-------------------+
| Department   | Number_Of_Employees |
+--------------+-------------------+
| Finance      |           5       |
| Production   |           8       |
+--------------+-------------------+

Explanation

In this example, the SQL code is grouping the records by department, counting the employees in each department and using the HAVING clause to filter out departments where there are fewer than 3 employees. The output shows the departments where there are more than 3 employees.

Example

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

Output

department	count
Sales	8
Marketing	6

Explanation

The above SQL statement selects the department and the count of employee_id from the employees table. It groups the results by department and then filters the groups having more than 5 employee_id using the HAVING clause.

Example

SELECT Department, COUNT(EmployeeId) AS NumberOfEmployees
FROM Employees
GROUP BY Department
HAVING COUNT(EmployeeId) > 5;

Output

| Department   |  NumberOfEmployees |
|--------------|-------------------|
| Sales        |        8          |
| IT           |        6          |

Explanation

The earlier provided example exhibits a SQL Server HAVING clause usage, a statement that allows filtering after the GROUP BY clause. The query returns departments that have more than five employees. The COUNT(EmployeeId) function counts the number of employees in each department, and the HAVING clause filters out the departments with employees count not exceeding five.

Example

SELECT COUNT(order_id), customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 5;

Output

COUNT(ORDER_ID)  CUSTOMER_ID
---------------- ----------------
6                102
7                103
8                105

Explanation

The presented SQL query counts and retrieves the number of order_ids for each customer from the orders table, but only for those customers who have more than 5 orders.

Example

SELECT employee_id, COUNT(*) as project_counts
FROM projects
GROUP BY employee_id
HAVING COUNT(*) > 3;

Output

| employee_id | project_counts |
|-------------|----------------|
| E1023       | 4              |
| E1045       | 5              |

Explanation

In the above SQL statement, we select the employee_id and count the number of projects (project_counts) from the projects table. We group the data by employee_id and use the HAVING clause to return only those employees who are managing more than 3 projects. The output is a table showing employee IDs and the count of their projects, where the count is greater than 3.