Aggregate Functions

Aggregate Functions

Oracle Database defines several Aggregate Functions to get Summary Results like the Number of Records, Maximum Value of a particular Column etc

count(*) ➔ Returns The Number of Records
max(esal) ➔ Returns Maximum Salary
min(esal) ➔ Returns Minimum Salary

E.g

String sqlQuery="select count(*) from emp"; 
ResultSet rs =st.executeQuery(sqlQuery);
if(rs.next()) { 
   System.out.println(rs.getInt(1)); 
}

Note

  • If Number of Records is more, then we should use while Loop.
  • If Number of Records is only one then we should use if Statement.

Program

Display Number of Rows by SQL Aggregate Function count(*).

Note

Before going into the program, it is advised to go through JDBC Programming Basic Steps where the meaning of Connection, Statement etc. are discussed.

import java.sql.*;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class RowCountDemo { 
   public static void main(String[] args) throws Exception {
      String sqlQuery = "select count(*) from emp"; 
      Class.forName("oracle.jdbc.OracleDriver"); 
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger"); 
      Statement st = con.createStatement();
      ResultSet rs =st.executeQuery(sqlQuery);
      if(rs.next()) { 
         System.out.println(rs.getInt(1)); 
      }
      con.close();
   }
}

Select highest salaried Employee Information by using SQL Aggregate Function Max

import java.sql.*;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class RowCountDemo { 
   public static void main(String[] args) throws Exception {
      String sqlQuery = "select * from emp where esal in (select max(esal) from emp)"; 
      Class.forName("oracle.jdbc.OracleDriver"); 
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger"); 
      Statement st = con.createStatement();
      ResultSet rs =st.executeQuery(sqlQuery);
      if(rs.next()) { 
         System.out.println("Highest sal employee information"); 
         System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getDouble(3)+"\t"+rs.getString(4));
      }
      con.close();
   }
}
Aggregate Functions

Scroll to top