Create Groups of Data using Group By clause • Grouping by One Column • Grouping by More Than One Column • Illegal Queries Using Group Functions • Restricting groups using HAVING Clause • Nesting Group Functions.

 

EXERCISE : 6

AIM : Create Groups of Data using Group By clause
• Grouping by One Column
• Grouping by More Than One Column
• Illegal Queries Using Group Functions
• Restricting groups using HAVING Clause
• Nesting Group Functions.

Description :

GROUP by clause :

• The GROUP BY clause is used in a SELECT statement to collect data across multiple records and group the results by one or more columns.
• Sometimes it is required to get information not about each row, but about each group.
• Related rows can be grouped together by the GROUP BY clause by specifying a column as a grouping column.
• In the output table all the rows with an identical value in the grouping column will be grouped together.  Hence, the number of rows in the output is equal to the number of distinct values of the grouping column.

Grouping by More Than One Column

EXAMPLE :

create table employee(eno int,ename varchar2(10),job varchar2(10),salary int,deptno int);

select * from employee;

  Query:Total salary paid to each job in each department   

select Deptno,job,salary from employee;

     select deptno,job,sum(salary) Total_Salary from employee group by deptno,job;

Query: Total Salary paid to each job in each department excluding Assoc Prof

 

Grouping by one column :

EXAMPLE :

select deptno,sum(salary) from employee group by deptno;

 select deptno,count(*) from employee group by deptno;

Illegal Queries using Group functions :

EXAMPLE :

select deptno,ename,count(*) from employee group by deptno;

 select deptno,job,count(*) from employee group by deptno,job;

         select eno,job,count(*) from employee group by deptno,job;

Restricting groups using HAVING clause :

EXAMPLE :

select deptno,sum(salary) from employee group by deptno having sum(salary)>85000;

select deptno,sum(salary) from employee group by deptno having sum(salary)>5000;

Nesting group functions :

EXAMPLE :

select deptno,avg(slary) from employee group by deptno;

select max(avg(salary)) from employee group by deptno;

select Min(avg(salary)) from employee group by deptno;

select deptno,sum(salary) from employee group by deptno;

select Min(sum(salary)) from employee group by deptno;

select Max(sum(salary)) from employee group by deptno;

select deptno,count(salary) from employee group by deptno;

select max(count(salary)) from employee group by deptno;

 

VIVA QUESTIONS:

1. Illustrate the process of  Grouping by One Column
2. Examples of Illegal Queries Using Group Functions
3. How to Rest groups using HAVING Clause?
4. How to use Nesting Group Functions?