Oracle Group By Clause

The GROUP BY clause groups data from multiple record and returns one row for each group. On other hand, it reduces the number of rows in the result set. The GROUP BY clause must appear after the WHERE clauses.

Oracle GROUP BY CLAUSE Syntax

To group the data from multiple record into one row for each group, use the following syntax:

Syntax

SELECT col1, col2, ...,
aggregate_function (col)   
FROM tablename WHERE condition 
GROUP BY col1, col2,...

Parameters:

tablename: The table name from which you want to fetch the records.

condition: To specify the conditions that must be fulfilled for the records to be selected.

GROUP BY: To sorts data set in descending order.

aggregate_function: It specifies the aggregate functions i.e. SUM, COUNT, MIN, MAX or AVG functions.

Oracle GROUP BY CLAUSE example

The following Oracle statement is used to group "firstName" from "tblCustomerLoan" table:

Example

SELECT SUM(workHover),firstName ,lastName, address, cellNo 
FROM tblCustomerLoan 
GROUP BY firstName;

Note:

In the above example, we have fetched records from "tblCustomerLoan" table with GROUP BY on "firstName". As you can see, the GROUP BY clause returns sum of "workHover" values.

Oracle GROUP BY CLAUSE example with COUNT function

The following Oracle statement is used to group "firstName" and count repetitive "firstName" in the column "firstName" from "tblCustomerLoan" table:

Example

SELECT firstName, lastName, address, count(firstName) AS Name 
FROM tblCustomerLoan 
GROUP BY firstName;

Note:

In the above example, we have grouped "firstName" and count repetitive "firstName" in the column "firstName" from "tblCustomerLoan" table. As you can see, the GROUP BY clause returns count for each repetitive "firstName".

Oracle GROUP BY CLAUSE example with COUNT function & JOIN

The following Oracle statement is used to group "banks" name and count repetitive "banks" name in the column "banks" from "tblbankdemataccountcharges" table:

Example

SELECT B.banks, A.accountName, count(B.banks) AS Count 
FROM tblbankdemataccountcharges A 
INNER JOIN tblbankheadquartersandaddress B 
GROUP BY B.banks LIMIT 15;

Note:

In the above example, we have grouped "banks" name and count repetitive "banks" name in the column "banks" from "tblbankdemataccountcharges" table with INNER JOIN "tblbankheadquartersandaddress" table. As you can see, the GROUP BY clause returns count for each repetitive "banks" name.

« Previous