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;
