SQL Clauses: HAVING Clause
The HAVING clause in SQL is used with the GROUP BY clause to restrict the results returned to only those records where the condition specified in the HAVING clause evaluates to true.
Syntax
The basic syntax of the HAVING clause in SQL is as follows:
SELECT column1, column2, column3, ...
FROM table
WHERE condition
GROUP BY column1, column2, column3, ...
HAVING condition;
Example
Suppose we have a table "employees" with columns "name", "department", and "salary". We want to find the average salary of employees in each department, but only for departments where the average salary is greater than 5000. We can use the following SQL query:
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 5000;
Output
The output of the above SQL query would be a table with two columns:
| department | AVG(salary) |
|---|---|
| Sales | 6000 |
| Marketing | 5500 |
| HR | 6500 |
Explanation
In the above example, we first group the records in the "employees" table by department using the GROUP BY clause. Then, we use the AVG aggregate function to calculate the average salary of employees in each department. Finally, we use the HAVING clause to restrict the results to only those departments where the average salary is greater than 5000.
Use
The HAVING clause is commonly used in SQL queries that involve aggregate functions like SUM, COUNT, AVG, and MAX. It allows you to filter the results of these functions based on certain conditions. For example, you may want to find the total sales for each region where the total sales are greater than a certain amount.
Important Points
- The HAVING clause is used with the GROUP BY clause to filter the results of aggregate functions.
- The HAVING clause is executed after the GROUP BY clause.
- The condition specified in the HAVING clause must be a valid SQL expression that evaluates to a boolean value.
- The HAVING clause cannot be used without the GROUP BY clause.
Summary
The HAVING clause in SQL is a powerful feature that allows you to filter the results of aggregate functions based on certain conditions. It is commonly used in SQL queries that involve grouping and summarizing data. When used correctly, the HAVING clause can help you extract valuable insights from your data and make more informed decisions.