What is Having?

The HAVING clause is used in the SELECT statement to specify filter conditions for a group of rows or aggregates. The MySQL HAVING clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE. The HAVING clause is often used with the GROUP BY clause to filter groups based on a specified condition. If you omit the GROUP BY clause, the HAVING clause behaves like the WHERE clause. The Having is also used for filtering the data just like the where clause. it filters the rows from the intermediate result set that is built by using the FROM, WHERE, or GROUP BY clauses in a SELECT statement. The Having Clause in MySQL is typically used with a GROUP BY clause.

Syntax

SELECT expression1, expression2, ... expression_n, aggregate_function (expression) FROM tables [WHERE conditions] GROUP BY expression1, expression2, ... expression_n HAVING condition;

MySQL evaluates the HAVING clause after the FROM, WHERE, SELECT and GROUP BY clauses and before ORDER BY, and LIMIT clauses:

SELECT ordernumber, SUM(quantityOrdered) AS itemsCount, SUM(priceeach*quantityOrdered) AS total FROM orderdetails GROUP BY ordernumber HAVING total > 1000;

Why do we need Having Clause in MySQL?

The WHERE clause could not be used with aggregate data rows, hence the HAVING clause was added to SQL. The purpose of the HAVING clause is to filter the group rows produced by the GROUP BY clause. The HAVING clause is the same as the WHERE clause. The only difference is WHERE clause FILTERS the intermediate data results, while the HAVING clause operates on group rows. Likewise, WHERE clause, we can use conditions and operators with the HAVING clauses to build complex SQL statements.

aggregate_function: It can be any of the aggregate functions such as SUM, COUNT, MIN, MAX, or AVG.

expression1, expression2, expression_n: The expressions which are not encapsulated within an aggregate function must be included in the GROUP BY clause.

Where Conditions: It is optional. If you want to retrieve selected records based on some conditions then you need to specify the conditions using the Where clause.

HAVING having_condition: The Having Clause Condition is used to add a further filter condition that can be applied only to the aggregated results to restrict the number of groups to be returned.

Example :- Filtering rows using WHERE clause

SELECT City, SUM(Salary) as Total_Salary FROM Employee WHERE City = 'India' GROUP BY City;

Example :- Filtering groups using the HAVING clause

SELECT City, SUM(Salary) as Total_Salary FROM Employee GROUP BY City HAVING City = 'India';

Example :- Using both Having and Where Clause in MySQL

SELECT City, SUM(Salary) as Total_Salary FROM Employee WHERE Gender = 'Male' GROUP BY City HAVING City = 'India';

Example :-

SELECT Department, AVG(Salary) AS 'Average salary' FROM Employee GROUP BY Department HAVING AVG(Salary) > 50000;

SUM function

SELECT product, SUM(quantity) AS "Total quantity" FROM order_details GROUP BY product HAVING SUM(quantity) > 10;

COUNT function

SELECT product, COUNT(*) AS "Number of orders" FROM order_details WHERE category = 'produce' GROUP BY product HAVING COUNT(*) > 20;

MIN function

SELECT department, MIN(salary) AS "Lowest salary" FROM employees GROUP BY department HAVING MIN(salary) < 50000;

MAX function

SELECT department, MAX(salary) AS "Highest salary" FROM employees GROUP BY department HAVING MAX(salary) > 25000;

Example

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC;

Previous Next

Review & Rating

0.0 / 5

0 Review

(0)

Most Popular Tutorials

What is Having?

PHP

PHP OOPs

Codeigniter

Laravel

NODE.JS

Javascript

0.0 / 5

0 Review

Write Review Here

Most Popular Tutorials

Important Link