The HAVING Clause empowers you to indicate conditions that channel which gathering results show up in the outcomes.
The WHERE proviso places conditions on the chose segments, though the HAVING statement places conditions on gatherings made by the GROUP BY statement.
Syntax
Following is the grammar of the HAVING provision −
ij> SELECT column1, column2 . . . from table_name GROUP BY column having
condition;
Example
Assume, we have a table named Employees in the information base with the 13 records as demonstrated beneath −
ID |NAME |SALARY |LOCATION
------------------------------------------------------------------
1 |Amit |30000 |Hyderabad
2 |Rahul |39000 |Lucknow
3 |Kalyan |40000 |Vishakhapatnam
4 |Renuka |50000 |Hyderabad
5 |Archana |15000 |Vishakhapatnam
6 |Krishna |40000 |Hyderabad
7 |Trupthi |45000 |Vishakhapatnam
8 |Raghav |12000 |Lucknow
9 |Radha |50000 |Delhi
10 |Anirudh |15000 |Mumbai
11 |Tara |45000 |Kochin
12 |Sucharita |44000 |Kochin
13 |Rizwan |20000 |Lucknow
The accompanying question shows greatest pay rates of the workers in the area which have in any event 3 representatives −
ij> SELECT Location, MAX(Salary) from Employees GROUP BY Location having
count(Location)>=3;
This produces the accompanying yield −
LOCATION |2
------------------------------------------------------------
Hyderabad |50000
Lucknow |39000
Vishakhapatnam |45000
3 rows selected
Sorting Data using JDBC program
This part shows you how to utilize Having a condition in the Apache Derby information base utilizing JDBC application.
In the event that you need to demand the Derby network worker utilizing network customer, ensure that the worker is going. The class name for the Network customer driver is org.apache.derby.jdbc.ClientDriver and the URL is jdbc:derby://localhost:1527/DATABASE_NAME;create=true;user=USER_NAME;passw ord=PASSWORD"
Follow the means offered underneath to sort the records of a table in Apache Derby
Step 1: Register the driver
To speak with the data set, above all else, you need to enroll the driver. The forName() technique for the Class acknowledges a String esteem speaking to a class name, and loads it in to the memory, which naturally enlists it. Register the driver utilizing this strategy.
Step 2: Get the connection
By and large, the initial step we do to impart to the data set is to associate with it. The Connection class speaks to the actual association with an information base worker. You can make an association object by summoning the getConnection() strategy for the DriverManager class. Make an association utilizing this strategy.
Step 3: Create a statement object
You need to make a Statement or PreparedStatement or, CallableStatement objects to send SQL proclamations to the data set. You can make these utilizing the techniques createStatement(), prepareStatement() and, prepareCall() separately. Make both of these articles utilizing the suitable technique.
Step 4: Execute the query
In the wake of making an assertion, you need to execute it. The Statement class gives different techniques to execute an inquiry like the execute() strategy to execute an explanation that profits more than one outcome set. The executeUpdate() technique executes questions like INSERT, UPDATE, DELETE. The executeQuery() technique brings information back. Use both of these strategies and execute the assertion made beforehand.
Example
Following JDBC model exhibits how to utilize Group By statement and perform CURD procedure on a table in Apache Derby utilizing JDBC program. Here, we are interfacing with an information base named sampleDB (will make in the event that it doesn't exist) utilizing the installed driver.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
public class HavingClauseExample {
public static void main(String args[]) throws Exception {
//Registering the driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
//Getting the Connection object
String URL = "jdbc:derby:sampleDB;create=true";
Connection conn = DriverManager.getConnection(URL);
//Creating the Statement object
Statement stmt = conn.createStatement();
//Creating a table and populating it
stmt.execute("CREATE TABLE EmployeesData( "
+ "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "
+ "Name VARCHAR(255), "
+ "Salary INT NOT NULL, "
+ "Location VARCHAR(255), "
+ "PRIMARY KEY (Id))");
stmt.execute("INSERT INTO EmployeesData(Name, Salary, Location) "
+ "VALUES ('Amit', 30000, 'Hyderabad'), "
+ "('Rahul', 39000, 'Lucknow'), "
+ "('Renuka', 50000, 'Hyderabad'), "
+ "('Archana', 15000, 'Vishakhapatnam'), "
+ "('Kalyan', 40000, 'Hyderabad'), "
+ "('Trupthi', 45000, 'Vishakhapatnam'), "
+ "('Raghav', 12000, 'Lucknow'), "
+ "('Suchatra', 33000, 'Vishakhapatnam'), "
+ "('Rizwan', 20000, 'Lucknow')");
//Executing the query
String query = "SELECT Location, MAX(Salary) "
+ "from EmployeesData GROUP BY Location having "
+ "count(Location)>=3";
ResultSet rs = stmt.executeQuery(query);
while(rs.next()) {
System.out.println(rs.getString(1));
System.out.println(rs.getString(2));
System.out.println(" ");
}
}
}
Output
On executing the above program, you will get the accompanying yield −
Hyderabad
50000
Lucknow
39000
Vishakhapatnam
45000