public List<Agent> getClientsPerAgent() { List<Agent> agent_list = null; try { conn = JDBC_Conn.getConnection(); String sql = "select agent_id, first_nm, last_nm, phone_number, bb.num from agents natural join (select agent_id, count(booking_id) num from bookings group by agent_id) bb order by bb.num desc"; stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery(); while (rs.next()) { if (agent_list == null) agent_list = new ArrayList<Agent>(); Agent agent = new Agent(); agent.setAgent_id(rs.getString("agent_id")); agent.setPhone_number(rs.getString("phone_number")); agent.setFirst_nm(CommUtils.initCap(rs.getString("first_nm"))); agent.setLast_nm(CommUtils.initCap(rs.getString("last_nm"))); agent.setNbr_clients(rs.getInt("num")); agent_list.add(agent); } } catch (Exception e) { e.printStackTrace(); throw new DAOException(); } finally { JDBC_Conn.releaseConnection(conn, stmt, rs); } return agent_list; }