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