In SQL, The HAVING clause is used in conjunction with the GROUP BY clause to filter the results of a grouped query based on a specified condition.
It is similar to the "WHERE" clause but is used specifically with grouped data.
The "HAVING" clause is particularly useful when we want to filter the result set based on aggregate values (like averages, counts, and sums) after using the GROUP BY clause.
It is important to note that while the "WHERE" clause filters rows before they are grouped, the "HAVING" clause filters groups after they have been formed.
SELECT columnName1, aggregate_function(columnName2), FROM tableName GROUP BY columnName1, HAVING condition;
columnName1: The column by which you want to group the results.
aggregate_function(columnName2): An aggregate function applied to another column within each group.
tableName: The name of the table from which you want to retrieve data.
HAVING: Specifies the condition to filter the groups.
SELECT category, COUNT(*) AS category_group_wise FROM products GROUP BY category HAVING COUNT(*) > 2;
The above query groups the `products` on the category column and calculate the Count of each `category`.
Then we have added the `Having` Clause which will filter the results to include only groups with a `count` greater than `2`.
SELECT category, AVG(product_price) AS avg_price FROM products GROUP BY category HAVING avg_price > 3000;
The above query groups the `products` on the category column and calculates the `AVERAGE` price for each `category`.
Then we have added the `Having` Clause which will filter the results to include only groups with an `avg_price` greater than `3000`.
SELECT category, SUM(product_price) AS total_price_category_wise FROM products GROUP BY category HAVING SUM(product_price) > 4000;
The above query groups the `products` on the `category` column and calculates the SUM for each `category`.
Then we have added the `Having` Clause which will filter the results to include only groups with an SUM of `product_price` greater than `4000`.