SQL HAVING Clause
SQL HAVING Clause
The SQL HAVING clause is used in conjunction with the GROUP BY clause to filter the result set based on the results of aggregate functions. The HAVING clause allows us to specify a condition that must be met by the aggregated data in order for a row to be included in the result set.
The syntax for the HAVING clause is as follows:
SELECT column1, column2, aggregate_function(column3)
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING aggregate_function(column3) condition;
In this syntax, the HAVING clause is placed after the GROUP BY clause and specifies the condition that must be met by the aggregate function.
Here’s an example of using the HAVING clause:
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
In this example, we’re selecting the department and average salary for each department, but only including departments where the average salary is greater than $50,000. The HAVING clause is used to filter the result set based on the result of the AVG function.
It’s important to note that the HAVING clause can only be used with aggregate functions, since it is used to filter the result set based on the results of those functions. Any non-aggregate columns that we want to include in the SELECT statement must also be included in the GROUP BY clause.
Here’s an example of a query that includes a non-aggregate column without including it in the GROUP BY clause:
SELECT department, AVG(salary), name
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
In this example, the name column is not included in the GROUP BY clause or used in an aggregate function, so the query will fail. To fix this, we need to either include the name column in the GROUP BY clause or use an aggregate function to include it in the SELECT statement.