HAVING clause in MySQL is used to filter the results of a
GROUP BY query based on a specified condition. The
HAVING clause is similar to the
WHERE clause, but while the
WHERE clause filters individual rows, the
HAVING clause filters groups of rows.
The syntax for using the
HAVING clause is as follows:
SELECT column1, column2, ..., aggregate_function(column) FROM table_name GROUP BY column1, column2, ... HAVING condition;
In this syntax, the
column2, and so on, represent the columns by which you want to group the data. The
aggregate_function is the function that you want to apply to the grouped data, such as
column argument for the aggregate function is the column that you want to perform the calculation on. The
condition is the condition that you want to apply to the grouped data.
For example, if you have a table called
sales with columns
sales_amount, and you want to find the regions where the total sales amount is greater than $1,000, you can use the
HAVING clause as follows:
SELECT region, SUM(sales_amount) FROM sales GROUP BY region HAVING SUM(sales_amount) > 1000;
This query will group the rows by region and calculate the sum of the
sales_amount column for each group. The
HAVING clause will filter the results to show only the regions where the total sales amount is greater than $1,000.
It’s important to note that the
HAVING clause must be used after the
GROUP BY clause and that it can only be used with aggregate functions. Any non-aggregate columns must be included in the
GROUP BY clause.