Group by clause in Oracle

        Group by clause is a clause is a clause of select statement responsible to logicallysegregate of select statement responsible to logically segregate the table data in diff diff blocks.

This clause is only meant for multirow function.

Syntax of select using group by:

Select <exp>

From<t_name>

[Where<cond>]

[group by<col_name>[,col_name,…………..n]];

Q. Write a query which will display department wise sum(sal), max(sal), min(sal)

Select sum(sal), max(sal), min(sal)

From emp

Group by deptno;

NOTE:

        Only that column is allowed in select list, the column which is used in group by clause.

SQL> Select ename, sum(sal), max(sal),min(sal)

From emp

Group by deptno;                                      ERROR

Working with multiple column wise grouping:

Q. Write a query which will display deptwise , job wise  sum(sal),max(sal),min(Sal)

A.   Select deptno, job , sum(sal),max(sal), min(sal) from emp

Group by deptno, job;      (multirow)

Working with count():

Count is a multirow() responsible to count the no. of values of a column or no. of records of a group. If can accept two types of arguments,*,col_name.

*à counting the no. of records

Col_name- counting the no. of column values.

Select count(*) from emp;

Ans 14

Select count(mgr) from emp ;

Ans 13

Q. Write query  which will display dept wise , job wise, sum(sal),max(sal), min(Sal) and the no. of employee  working in each group.

Select deptno, max(sal),min(sal),sum(sal), count(*) from emp

Group by deptno,job;