The HAVING clause in SQL Server is used to specify the search condition for a group or an aggregate. It can only work with the SELECT statement and is usually used with the GROUP BY clause. It enables us to filter which group results appear in the results. We used this clause instead of the WHERE clause because it places a condition on groups created by the GROUP BY clause, while the WHERE clause places a condition on the selected columns.
Each HAVING condition in SQL can compare one group column or aggregate expression to another group aggregate expression or a constant. We may use HAVING to place conditions on both column and aggregate values in the group list.
Benefits of Having Clause
The HAVING clause validates groups in the same way as the WHERE clause validates individual rows. The main benefit of using a HAVING clause is that aggregates can be included in the search condition, whereas aggregates cannot be used in a WHERE clause’s search condition.
Syntax
The following are the basic syntax of the HAVING clause in SQL Server:
SELECT column_list,
aggregate_function (expression)
FROM table_name
GROUP BY group_list
HAVING search_conditions;The parameter descriptions of the above syntax are explained below:
- First, we will select the column names that we want in the result. Here, we can also specify the aggregate function such as SUM, COUNT, MIN, MAX, or AVG.
- Second, we will write the table name from which column will be selected.
- Third, we use the GROUP BY clause to summarize the rows into groups.
- Fourth, we use the HAVING clause with single or multiple conditions to the groups evaluated by the GROUP BY clause. The final result included only those groups that make the evaluation of the condition TRUE. If the condition is found FALSE or UNKNOWN, the groups are filtered out.
HAVING Clause Example
Let us understand the HAVING clause with examples that helps us to use it with the different SQL aggregate functions. Suppose we have a table named employees_info that contains the following data:
We will use this table to demonstrate the HAVING clause with different examples.
Example1: The below query will use the SUM function with the HAVING clause. It returns the employee name and SUM of their working hours whose working hours are more than 6 hours:
SELECT name, SUM(working_hours) AS "Total working hours"
FROM employee_info
GROUP BY name
HAVING SUM(working_hours) > 6;Executing the query will return the below output:
Example2: The below query will use the COUNT function with the HAVING clause. This statement counts the working hour of those employees whose salary is greater than 30000:
SELECT name, COUNT(working_hours) AS total_employee
FROM employee_info WHERE salary>30000
GROUP BY name
HAVING COUNT(working_hours)>= 1;Executing the statement will return the below output:
Example3: The below query will use the MIN function with the HAVING clause. It returns the minimum working hour of each employee whose minimum working hour is greater than 10.
SELECT name, MIN(working_hours) AS total_working_hours
FROM employee_info
GROUP BY name
HAVING MIN(working_hours)>= 10;Executing the statement will return the below output where we see the employee name along with their minimum working hours:
Example4: The below query will use the MAX function with the HAVING clause. It returns the maximum working hour of each employee whose maximum working hour is less than 12.
SELECT name, MAX(working_hours) AS total_working_hours
FROM employee_info
GROUP BY name
HAVING MAX(working_hours) <= 12;Executing the statement will return the below output where we see the employee name along with their maximum working hours less than 12:
Example5: The below query will use the AVERAGE function with the HAVING clause. It returns the occupation name whose average salary is more than and equals 30000.
SELECT AVG(salary) AS average_salary, occupation
FROM employee_info
GROUP BY occupation
HAVING AVG(salary) >= 30000;Executing the statement will return the below output:
This article will describe the HAVING clause, how it is different from the WHERE clause, and how it works with the aggregate functions SUM, COUNT, MAX, MIN, and AVERAGE.
Leave a Reply