Recent Posts

Tuesday, 13 November 2018

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

Next Tutorial : Working with MySQL Database

Previous Tutorial : JDBC - Select records from a table

No comments:

Post a Comment