SQL HAVING Clause

Purpose of  SQL HAVING Clause

The HAVING clause is generally used with the GROUP BY clause for specifying a condition for a group or aggregated data.
  • The WHERE clause is used for specifying the search condition, however, you cannot use aggregate functions with the WHERE clause.
  • The HAVING clause can only be used with the SELECT statement.
  • I will show you using the SQL HAVING clause with and without the GROUP BY clause for specifying the conditional search.

An example of HAVING clause with aggregate function

Let me start with an example of a HAVING clause with the SUM aggregate function. We have an example table for storing the employee salaries paid every month.

In the SELECT statement, the SUM function is used for getting the total salary paid to each employee (in our first query).

In the second query, the HAVING clause is used to return only those employees whose sum of paid salary is greater than or equal to 5000:

Query:

SELECT SUM(emp_sal_paid) As "Total Paid"

FROM sto_emp_salary_paid

HAVING SUM(emp_sal_paid) >= 5000;

SQL HAVING simple

The purpose of this example is to show that the HAVING clause can also be used without the GROUP BY clause, however, the result is not meaningful. Now let us use it with the GROUP BY clause.

Note: You may test this and subsequent queries in MS SQL Server (tested) and MySQL as well.

The example of HAVING with GROUP BY clause

  • In this query, the SUM of salaries for each employee is calculated by using the GROUP BY clause.
  • After the GROUP BY, the HAVING clause is used where the aggregate SUM function is used.
  • There we set the condition to return only those records whose total is greater than or equal to 2500.

The Query:

SELECT SUM(emp_sal_paid) As "Total Paid", emp_name As "Employee Name"

FROM sto_emp_salary_paid

GROUP BY emp_name

HAVING SUM(emp_sal_paid) >= 2500;

SQL HAVING GROUP BY

The example of using the COUNT function with HAVING clause

The COUNT function gets the number of rows returned by the SELECT statement. You may use this with the HAVING clause for defining a condition for returning the aggregated result.

In this example, I have used two queries.

  1. The first is with only the GROUP BY clause that returns number of times salaries are paid to each employee with their names. There, I used the COUNT and SUM functions with the GROUP BY clause.
  2. In the second query, I used the HAVING clause as well for returning only those records whose count of paid salary is two or more.

The queries:

SELECT SUM(emp_sal_paid) As "Total Paid", COUNT(emp_sal_paid) As "Times Salary Paid", emp_name As "Employee Name"

FROM sto_emp_salary_paid

GROUP BY emp_name



SELECT SUM(emp_sal_paid) As "Total Paid", COUNT(emp_sal_paid) As "Times Salary Paid", emp_name As "Employee Name"

FROM sto_emp_salary_paid

GROUP BY emp_name

HAVING COUNT(emp_sal_paid) >= 2;

The result-sets:

SQL HAVING COUNT

You can see that the first query returned five employees’ records while the second only had three records.

Using MAX function with HAVING clause

Similarly, you may use the MAX function with the HAVING clause. The following query returns the employee names and maximum salaries of employees who are paid more than 4500.

The query:

SELECT MAX(emp_sal_paid) As "Maximum Salary", emp_name As "Employee Name"

FROM sto_emp_salary_paid

GROUP BY emp_name

HAVING MAX(emp_sal_paid) > 4500;

SQL HAVING MAX

You cannot specify this MAX expression in the WHERE clause.
Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we solve the mysteries of coding together!