MySQL - Having Clause

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

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) < 4540 ORDER BY NAME;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});             

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) < 4540 ORDER BY NAME;";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()){
              String name = rs.getString("Name");
              String total_salary = rs.getString("total_salary");
              System.out.println("Name: " + name + ", Total_Salary: " + total_salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}                          

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) < 4540 ORDER BY ADDRESS"""
cursorObj.execute(having_clause_query)
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()                                

Output

Following is the output of the above code −

('Mumbai', Decimal('1200.00'))