Purpose of SQL HAVING Clause
- 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;
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.
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;
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.
- 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.
- 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:
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;