MySQL Having Clause

The MySQL HAVING Clause is used to filter grouped rows in a table based on conditions.

This clause is used with the GROUP BY clause to group the rows based on one or more columns and then filter them based on the conditions specified in the HAVING clause. So, the HAVING clause must always be followed by the GROUP BY clause.

The HAVING clause was added to MySQL because the WHERE keyword cannot be used with aggregate functions such as COUNT(), SUM(), AVG(), etc.

This clause is similar to the MySQL WHERE clause. The difference between both of them is that the WHERE clause filters individual rows in a table, whereas the HAVING clause filters grouped rows based on conditions.

Syntax

Following is the basic syntax of the HAVING clause in MySQL −

SELECT column1, column2, aggregate_function(column)
FROM table_name
GROUP BY column1, column2, ...
HAVING condition
ORDER BY column1, column2, ...;

Example

Let us begin with creating a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),   
   PRIMARY KEY (ID)
);

The following INSERT statement inserts 7 records into the above-created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Using the following query, we can verify whether the CUSTOMERS table is created or not −

SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

HAVING clause with ORDER BY clause

In MySQL, the HAVING clause filters the groups, and the ORDER BY clause sorts the results. When we used both of them together, HAVING is executed first, then the result set is sorted according to the ORDER BY criteria.

Example

In the following query, we are retrieving all the records from the CUSTOMERS table where the sum of their SALARY is less than 4540, ordered by their name in ascending order −

SELECT NAME, SUM(SALARY) as total_salary
FROM CUSTOMERS
GROUP BY NAME
HAVING SUM(SALARY) < 4540
ORDER BY NAME;

Output

The output for the query above is produced as given below −

HAVING clause with COUNT() function

We can use the MySQL HAVING clause in conjunction with the COUNT() function to filter the groups based on the number of rows they contain.

Example

In this query, we are fetching a record where the count of similar age is greater than or equal to 2.

SELECT AGE
FROM CUSTOMERS
GROUP BY age
HAVING COUNT(age) >= 2;

Output

There are two records in CUSTOMERS table with age 25, thus the output is 25 −

HAVING clause with AVG() function

The MySQL HAVING clause can also be used with the AVG() function to filter groups based on the average value of a specified column.

Example

In the following query, we are trying to return the names of the customers whose salary is greater than 3000 −

SELECT NAME, AVG(salary) as avg_salary
FROM customers
GROUP BY NAME
HAVING AVG(salary) > 3000;

Output

The output for the query above is produced as given below −

HAVING clause with MAX() function

In MySQL, we can also use the HAVING clause with MAX() function to filter groups based on the maximum value of a specified column.

Example

In this query, we are retrieving the customer names whose maximum SALARY is less than 4000 −

SELECT NAME, MAX(salary) as max_salary
FROM customers
GROUP BY NAME
HAVING MAX(salary) < 4000;

Output

On executing the given query, the output is displayed as follows −

Having Clause Using a Client Program

Besides using MySQL HAVING clause to filter grouped rows in a table based on conditions, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql = "SELECT EXPRESSION1, EXPRESSION2, ...EXPRESSION_N, 
AGGREGATE_FUNCTION(EXPRESSION) FROM TABLE_NAME 
[WHERE CONDITION] GROUP BY EXPRESSION1, 
EXPRESSION2.. EXPRESSION_N HAVING CONDITION";
$mysqli->query($sql);

sql= " SELECT column1, column2, aggregate_function(column) 
FROM table_name GROUP BY column1, column2, ... 
HAVING condition ORDER BY column1, column2, ...";   
con.query(sql);

String sql = "SELECT column1, column2, aggregate_function(column) 
FROM table_name GROUP BY column1, column2, ... 
HAVING condition ORDER BY column1, column2, ...";  
statement.executeQuery(sql);

having_clause_query = "SELECT column1, column2, aggregate_function(column) 
FROM table_name GROUP BY column1, column2 HAVING condition"
cursorObj.execute(having_clause_query);

Example

Following are the programs −

$dbhost = ''localhost'';
$dbuser = ''root'';
$dbpass = ''password'';
$dbname = ''TUTORIALS'';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf(''Connected successfully.
'');
$sql = ''SELECT tutorial_title, count(tutorial_id) AS tot_count FROM tutorials_tbl WHERE tutorial_id > 1 GROUP BY tutorial_title HAVING count(tutorial_id) > 1'';
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("Title: %s, Count: %d",  
         $row["tutorial_title"],
         $row["tot_count"]);
      printf("n");         
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Table records:
Title: Learn MySQL, Count: 2        

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating table
  sql = "CREATE TABLE CUSTOMERS(ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,GENDER VARCHAR (25),SALARY DECIMAL (18, 2),PRIMARY KEY (ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO CUSTOMERS VALUES(1, ''Ramesh'', 25, ''Male'', 2000.00),(2, ''Ramesh'', 25, ''Male'', 1500.00),(3, ''kaushik'', 25, ''Female'', 2000.00),(4, ''kaushik'', 20, ''Male'', 6500.00),(5, ''Hardik'', 25, ''Male'', 8500.00),(6, ''Komal'', 20, ''Female'', 4500.00),(7, ''Muffy'', 25, ''Male'', 10000.00);"
  con.query(sql);

  //Using HAVING Clause
  sql = "SELECT NAME, SUM(SALARY) as total_salary FROM CUSTOMERS GROUP BY NAME HAVING SUM(SALARY) 
   
Output
   
The output produced is as follows −
Connected!
--------------------------
[
  { NAME: ''Komal'', total_salary: ''4500.00'' },
  { NAME: ''Ramesh'', total_salary: ''3500.00'' }
]        

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class HavingClause {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT NAME, SUM(SALARY) as total_salary FROM CUSTOMERS GROUP BY NAME HAVING SUM(SALARY) 
   
Output
   
The output obtained is as shown below −
Table records: 
Name: Komal, Total_Salary: 4500.00
Name: Ramesh, Total_Salary: 3500.00        

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object 
cursorObj = connection.cursor()
having_clause_query = """SELECT ADDRESS, SUM(SALARY) as total_salary FROM CUSTOMERS
GROUP BY ADDRESS HAVING SUM(SALARY) 
Output
Following is the output of the above code −
(''Mumbai'', Decimal(''1200.00''))