Oracle (5) ------- grouping Function

The grouping function acts on a group of data and returns a value for a group of data. Group function type AVG average count returns the total number of records not empty Max maximum Min SQL> select AVG (salary), max (salary), min (salary), sum (salary) from employees; AVG (salary) max (salary) min (salary) sum (salary) ----------- 6461.68224 24000 2100 691400 stddev standard variance sum distinct (distinct) keyword count (distinct expr) returns the total number of records that are not empty and repeat expr SQL> select count (distinct employee_id) from employees; count (distinctemployee_id )----- --------------------- The null value is ignored in the 107 group of functions. Select AVG (commission_pct) from employeesnvl function makes the grouping function unable to ignore null values select AVG (nvl (commission_pct, 0) from employees grouping data: the group by clause syntax can use the group by clause to divide the data in the table into several groups. All columns not included in the group function in the select list should be included in the group by clause. SQL> select employee_id, AVG (salary) from employees group by employee_id; columns contained in the group by clause do not need to be included in the select list for use in the select list, columns not included in the group function must be included in the group by clause; otherwise, an error occurs: Row 1st error: ORA-00979: Not a group by expression. Note: you cannot use group functions in the WHERE clause (note ). You can use group functions in the having clause. Filter Group: use having to filter groups: 1. rows have been grouped. 2. Group functions are used. 3. select department_id, max (salary) from employees group by department_id having max (salary)> 10000 nested group function shows the maximum average wage SQL> select max (AVG (salary) from employees group by department_id; max (AVG (salary) ---------------- 19333.3333