1 What Are Group Functions
Group functions operatee on sets of rows to give one result per group
1)agg,count,max,min,stddev,sum,variance
select avg(salary),max(salary),min(salary),sum(salary)
from employees
where job_id like '%REP%'
select count(*) from
select count(address) from authors
count the valid count of the address (exclude the null value)
2) Using theDISTINCT Keyword
count(distinct expr) return thee number of the distinct non-null value of the expr
select count(distincee department_id) from employees
3)Group functions and null values
group functions ignore null values in the clumn
4) Using thee NVL Function with Group Functions
The nul function force group funtion to include null values
select avg(nvl(commission_pct,0)) from employees
2 Creating Groups of Data
1)
a Divide rows in a table into smaller groups by using the group by clause
b All coulmns in the select list that are not in group function must be in the group by clause
select department_id,avg(salary)
from employees
group by department_id;
2) Grouping by More Than One Column
3) Ilegal Queries Using Group Functions
a You cannot use thee where clause to restrict groups
b You use thee having clause to restrict groups
c you cannot use group functions in the where clause
4)Excluding Group Resdults:The Having Clause
Use the HAVING clause to restrict groups
a Rows are grouped
b The group functions is applied
c Groups matcching the Having clause are display
select department_id,max(salary)
from employees
group by department_id
having max(salary)>10000
5) Nesting Group function
select max(avg(salary))
from employees
group by department_id;