santosh-asp tutorial

Oracle GROUP BY Clause

In Oracle GROUP BY clause is used with SELECT statement to collect data from multiple records and group the results by one or more columns.

Syntax:-

  

    SELECT expression1, expression2, ... expression_n,   
    aggregate_function (aggregate_expression)  
    FROM tables  
    WHERE conditions  
    GROUP BY expression1, expression2, ... expression_n;

Example:- We create table named with Employee and insert some records as shown in below:

  
   CREATE TABLE Employee  
    (
    Empcode varchar2(30) primary key,
    Name varchar2(100),
    Age number(5),   
    Salary numeric(10,2),
    Email varchar2(200),   
    State varchar2(100)  
    CONSTRAINT Employee_pk PRIMARY KEY (Empcode)  
    );  


    //Insert some records as shown below:-

Insert into Employee(Empcode,Name,Age,Salary,Email,State) values('Emp1001','Santosh Kumar Singh',22,12000,'s@gmail.com','Bihar')
Insert into Employee(Empcode,Name,Age,Salary,Email,State) values('Emp1002','Reena kumari',25,15000,'r@gmail.com','Bihar')
Insert into Employee(Empcode,Name,Age,Salary,Email,State) values('Emp1003','Anu Singh',21,14000,'a@gmail.com','UP')
Insert into Employee(Empcode,Name,Age,Salary,Email,State) values('Emp1004','Gagan Agrawal',24,11000,'g@gmail.com','Haryana')
Insert into Employee(Empcode,Name,Age,Salary,Email,State) values('Emp1005','Suraj',21,13000,'su@gmail.com','Bihar')
Insert into Employee(Empcode,Name,Age,Salary,Email,State) values('Emp1006','Pramod kumar sah',27,14000,'pk@gmail.com','New Delhi')

Execute the GROUP BY Clause:-

  

select State, COUNT(*) as &quot;TotalNoEmployee&quot; from Employee group by State

Out Put:-