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;