The GROUP BY proviso is utilized with SELECT explanations. It is utilized to frame subsets in the event of indistinguishable information. Normally, this condition is trailed by ORDER BY proviso and set after the WHERE statement.
Syntax
Following is the grammar of GROUP BY proviso −
ij>SELECT column1, column2, . . . table_name GROUP BY column1, column2, . . .;
Example
Assume we have a table named Employees in the information base with the accompanying records −
ID |NAME |SALARY |LOCATION
------------------------------------------------------------------
1 |Amit |30000 |Hyderabad
2 |Rahul |39000 |Lucknow
3 |Renuka |50000 |Hyderabad
4 |Archana |15000 |Vishakhapatnam
5 |Kalyan |40000 |Hyderabad
6 |Trupthi |45000 |Vishakhapatnam
7 |Raghav |12000 |Lucknow
8 |Suchatra |33000 |Vishakhapatnam
9 |Rizwan |20000 |Lucknow
The accompanying SELECT assertion with GROUP BY proviso bunches the table dependent on the spot. It shows the aggregate sum of compensation given to representatives at an area.
ij> SELECT Location, SUM(Salary) from Employees GROUP BY Location;
This will create the accompanying yield −
LOCATION |2
-------------------------------------------------------
Hyderabad |120000
Lucknow |71000
Vishakhapatnam |93000
3 rows selected
Similarly, following inquiry finds the normal sum spent on the workers as pay in an area.
ij> SELECT Location, AVG(Salary) from Employees GROUP BY Location;
This will create the accompanying yield −
LOCATION |2
-----------------------------------------------------
Hyderabad |40000
Lucknow |23666
Vishakhapatnam |31000
3 rows selected
Group By clause JDBC example
This part shows you how to utilize Group By statement and perform CURD procedure on a table in Apache Derby data set utilizing JDBC application.
In the event that you need to demand the Derby network worker utilizing network customer, ensure that the worker is fully operational. 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 utilize Group By provision and perform CURD procedure on a table in Apache Derby
Step 1: Register the driver
To speak with the data set, most importantly, you need to enroll the driver. The forName() technique for the Class acknowledges a String esteem speaking to a class name loads it in to the memory, which consequently enrolls it. Register the driver utilizing this strategy.
Step 2: Get the connection
All in all, the initial step we do to convey to the information base is to interface with it. The Connection class speaks to the actual association with an information base worker. You can make an association object by conjuring the getConnection() technique 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 explanations to the information base. You can make these utilizing the strategies createStatement(), prepareStatement() and, prepareCall() separately. Make both of these items utilizing the suitable strategy.
Step 4: Execute the query
In the wake of making an assertion, you need to execute it. The Statement class gives different strategies to execute a question like the execute() strategy to execute an explanation that profits more than one outcome set. The executeUpdate() strategy is utilized to execute inquiries like INSERT, UPDATE, DELETE. The executeQuery() strategy brings information back. Use both of these strategies and execute the assertion made beforehand.
Example
Following JDBC model exhibits how to utilize Group By provision and perform CURD procedure on a table in Apache Derby utilizing JDBC program. Here, we are associating with an information base named sampleDB (will make in the event that it doesn't exist) utilizing the implanted driver.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
public class GroupByClauseExample {
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, SUM(Salary) from EmployeesData GROUP BY Location";
ResultSet rs = stmt.executeQuery(query);
while(rs.next()) {
System.out.println("Location: "+rs.getString(1));
System.out.println("Sum of salary: "+rs.getString(2));
System.out.println(" ");
}
}
}
Output
On executing the above program, you will get the accompanying yield −
Location: Hyderabad
Sum of salary: 120000
Location: Lucknow
Sum of salary: 71000
Location: Vishakhapatnam
Sum of salary: 93000