Back to JDBC Hub

Nearby lessons

33 of 37
JDBC - Update RecordsJDBC - Delete RecordsJDBC - Aggregate FunctionsJDBC - Menu Driven CRUDJDBC - Questions & Answers

JDBC - Aggregate Functions

📌 What You Will Learn
  • SQL aggregate functions: count, sum, avg, min, max
  • Real-time coding standards every JDBC program should follow
  • Setting up MySQL and running JDBC with it
  • Using try-with-resources to close things safely

Aggregate Functions is a fundamental part of database programming with JDBC. This lesson explains SQL Aggregate Functions, Program 1: count(*): number of rows and Program 2: max, min, sum, avg with complete, runnable code examples, clear step-by-step explanations, and common mistakes to avoid with exam-style MCQs at the end.

SQL Aggregate Functions

An aggregate function takes many rows and returns one summary value. The five important ones:

FunctionReturns
count(*)The number of rows in the table
sum(column)The total of a numeric column
avg(column)The average of a numeric column
min(column)The smallest value in a column
max(column)The largest value in a column
In simple words: An aggregate function squeezes many rows into one summary value. So a query like SELECT COUNT(*) returns exactly one row, and SUM, AVG, MIN and MAX behave the same way.

Program 1: count(*): number of rows

In simple words: A query with an aggregate function returns exactly one row. You call rs.next() once and then read the value with rs.getInt(1) — the 1 means the first and only column.
Example02
JCode Cell
1import java.sql.*;
2 
3public class RowCountDemo {
4 public static void main(String[] args) throws Exception {
5 Connection con = DriverManager.getConnection(
6 "jdbc:mysql://localhost:3306/company", "root", "password");
7 Statement st = con.createStatement();
8 
9 ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM employee");
10 rs.next(); // move to the only row
11 System.out.println("Number of rows: " + rs.getInt(1));
12 con.close();
13 }
14}
Output
Number of rows: 10

Program 2: max, min, sum, avg

Example03
JCode Cell
1import java.sql.*;
2 
3public class AggregateDemo {
4 public static void main(String[] args) throws Exception {
5 Connection con = DriverManager.getConnection(
6 "jdbc:mysql://localhost:3306/company", "root", "password");
7 Statement st = con.createStatement();
8 
9 ResultSet rs = st.executeQuery(
10 "SELECT MAX(salary), MIN(salary), SUM(salary), AVG(salary) FROM employee");
11 rs.next();
12 System.out.println("Max salary : " + rs.getDouble(1));
13 System.out.println("Min salary : " + rs.getDouble(2));
14 System.out.println("Total salary: " + rs.getDouble(3));
15 System.out.println("Avg salary : " + rs.getDouble(4));
16 con.close();
17 }
18}

Program 3: employee with the highest salary (sub-query)

Example04
JCode Cell
1ResultSet rs = st.executeQuery(
2 "SELECT * FROM employee WHERE salary IN (SELECT MAX(salary) FROM employee)");
3while (rs.next()) {
4 System.out.println(rs.getInt(1) + " " + rs.getString(2));
5}
📝 Key Takeaways
  • Aggregate functions: count, sum, avg, min, max — one summary value from many rows.
  • rs.next() then getInt(1) reads the single-row aggregate result.
  • Real-time standards: close resources, use PreparedStatement, use connection pools.
  • try-with-resources closes Connection, Statement, ResultSet automatically.
  • MySQL setup: create DB → create table → add rows → add connector JAR to classpath.

🧠 Test Your Knowledge

1 Questions
Progress: 0 / 1