Khóa học miễn phí MySQL – Having Clause nhận dự án làm có lương

MySQL – Having Clause

Table of content


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 −

ID NAME AGE ADDRESS SALARY
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

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 −

NAME total_salary
Kaushik 2000.00
Khilan 1500.00
Komal 4500.00
Ramesh 2000.00

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 −

AGE
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 −

NAME avg_salary
Chaitali 6500.000000
Hardik 8500.000000
Komal 4500.000000
Muffy 10000.000000

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 −

NAME max_salary
Ramesh 2000.00
Khilan 1500.00
Kaushik 2000.00

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 −

To filter grouped rows in a table based on conditions through PHP program, we need to execute SELECT statement with HAVING clause using the mysqli function query() as follows −

$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);

To filter grouped rows in a table based on conditions through Node.js program, we need to execute SELECT statement with HAVING clause using the query() function of the mysql2 library as follows −

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

To filter grouped rows in a table based on conditions through Java program, we need to execute SELECT statement with HAVING clause uusing the JDBC function executeUpdate() as follows −

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

To filter grouped rows in a table based on conditions through Python program, we need to execute SELECT statement with HAVING clause using the execute() function of the MySQL Connector/Python as follows −

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();

Output

The output obtained is as follows −

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''))

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc