Aggregate or Group Functions:
Ø These functions return a single row
based on groups of rows.
Ø These functions can appear in select
lists and having clauses only.
Ø These functions operate on sets of
rows to give one result per group.
Ø The sets may be the whole table or
the table split into groups.
Guidelines to use group functions:
Ø DISTINCT makes the function to
consider only non-duplicate values.
Ø ALL makes the function to consider
every value including duplicates.
Syntax: GroupFunctionName(distinct /all col)
Ø The data types for arguments may be
CHAR, VARCHAR2, NUMBER OR DATE.
Ø All group functions except count (*)
ignore NULL values. To substitute a value for NULL value, Use the NVL Function.
Ø When a group function is declared in
a select list, no single row columns should be declared.
Ø When a group function is declared in
a select list, other columns can be declared, but they should be grouped
columns, and all the non-functional columns should be declared into group by clause.
Average Function:
Syntax:
AVG (Distinct /All cols)
Ø It Returns the AVERAGE value of
column.
Ø It ignores NULL values.
Ex:
·
Select
avg(sal), avg(distinct sal) from emp;
·
Select
avg(comm), avg(distinct comm) from emp;
Sum Function:
Syntax: Sum(distinct/all col)
Ø It returns the sum value of column.
Ø It ignores NULL values.
Ex:
·
Select
sum(sal), sum(distinct sal) from emp;
·
Select
sum(comm), sum(distinct comm) from emp;
Maximum Function:
Syntax: Max(Distinct /All col)
Ø It returns the maximum value of
column.
Ø It ignores NULL values.
Ex:
·
Select
max(sal), max(distinct sal) from emp;
·
Select
Max(comm), max(distinct comm) from emp;
Minimum Function:
Syntax: Min(distinct / all col)
Ø It returns the minimum value of the
column.
Ø It ignores NULL values.
Ex:
·
Select
min(sal), min(distinct sal) from emp;
·
Select
min(Comm), min(distinct Comm) from emp;
Standard Deviation Function:
Syntax: STDDEV(distinct /All col)
Ø It returns the standard deviation of
the column.
Ø It ignores NULL values.
Ex:
·
Select
stddev(sal), stddev(distinct sal) from emp;
·
Select
stddev(Comm), stddev(distinct Comm) from emp;
Variance Function:
Syntax: variance(distinct / All col)
Ø It returns the variance of N.
Ø It ignores the NULL values.
Ex:
·
Select
variance(sal), variance(distinct sal) from emp;
·
Select
variance(Comm), variance(distinct variance) from emp;
Count Function:
Syntax: Count(*/Distinct/All col)
Ø It returns the number of rows in the
query.
Ø It ‘*’ is used returns all rows,
including duplicated and NULL’s.
Ø It can be used to specify the count
of all rows or only distinct values of col.
Ex:
·
Select
count(*) from emp;
·
Select
count(job), count(distinct job) from emp;
·
Select
count(sal), count(Comm) from emp;
·
Select
count(empno), count(distinct MGR) from emp;
Creating Groups of Data:
Ø The group by clauses is used to
decide the rows in a table into groups.
Syntax1:
·
Select
columnName1, columnName2,… From TableName where condition(s) group by
columnNmae(s) Order by ColumnName(s);
Syntax2:
·
Select
columnName1, GRP_FUN(Column) from TableName where Condition(s) Group by ColumnName(s)
Order by Column(s);
Guidelines to use Group by clause:
Ø If the group function is included in
a select clause, we should not use individual result columns.
Ø The extra non group functional
columns should be declared in the group by clause.
Ø Using where clause, rows can be pre
excluded before dividing them into groups.
Ø Column aliases cannot be used in
group by clause.
Ø By default, rows are sorted by
ascending order of the columns included in the group by list.
Ø The column applied upon group by
clause need not be part of select list.
Ex:
·
Select
deptno from emp group by deptno;
·
Select
job from emp group by job;
·
Select
mgr from emp group by mgr;
·
Select
to_char(hiredate, ’YYYY’) yeargroup from emp group by to_char(hiredate, ’YYYY’);
·
Select
to_char(hiredate, ’Month’) Monthgroup from emp group by to_char(hiredate, ’Month’);
·
Select
to_char(hiredate, ’Month’) Monthgroup from emp where to_char(hiredate, ’Month’)
<> ’September’ Group by to_char(hiredate, ’Month’);
Creating Group wise summaries:
Ex:
·
Select
deptno, avg(sal) from emp group by deptno;
·
Select
deptno, avg(sal) from group by deptno order by avg(sal);
·
Select
deptno, min(sal), max(sal) from emp group by deptno, job;
Note:
Ø The above specification falls under
the principle of groups within groups.
Ex:
·
Select
deptno, min(sal), max(sal) from emp where job= ’CLERK’ Group by Deptno;
·
Select
deptno, sum(sal), avg(sal) from emp where job = ’CLERK’ Group by deptno;
Excluding Groups of Results:
Having Claus:
Ø It is used to specify which groups
are to be displayed.
Ø The clause is used to filter data
that is associated with group functions.
Syntax:
·
Select
Column, Group_Function From Table [where condition(s)] [ Group by
Group_by_expr] [Having Group_condition(s)] [Order by column_name / Alias ];
Steps Performs By Having Clause:
Ø First the rows are grouped.
Ø Second the group function is applied
to the identified groups.
Ø Third the groups that match the
criteria in the having clause are displayed.
Ø The having clause can precede group
by clause, but it is more logical to declare it after group by clause.
Ø Group by clause can be used, without
a group functions in the select list.
Ø If rows are restricted based on the
result of a group function, we must have a group by clause as well as the
having clause.
Ø Existence of group by clause does
not guarantee the existence of having clause, but the existence of having
clause demands the existence of group by clause.
Ex:
·
Select
deptno, avg(sal) from emp group by deptno, having max(sal) > 2900;
·
Select
job, sum(sal) payroll from emp where job not like ’SALES%’ group by job having
sum(sal) > 5000 order by sum(sal);
·
Select
Deptno, min(sal), max(sal) from emp where job=’CLERK’ group by deptno having min(sal) < 1000;
·
Select
depnto, sum(sal) from emp group by deptno having count(deptno) > 3;
·
Select
deptno, avg(sal), sum(sal), max(sal),min(sal) from emp group by deptno Having
avg(sal) > 2500;
Nesting of Group Functions:
Ø Group Functions can be nested to a
depth of two levels.
Ex:
·
Select
max(avg(sal)) from emp group by deptno;
·
Select
max(sum(sal)), min(sum(sal)) from emp group by deptno;
·
Select
max(sum(sal)), min(avg(sal)) from emp group by job;