Aggregate functions in Oracle

The aggregate function in Oracle performs computation on a column in a group of rows and returns a single value. The aggregate function ignores NULL values. Aggregate functions are often used together with the group by clause of SELECT statements, so they are also called grouping functions sometimes. Introduction to grouping functions: grouping Functions Act on a group of data and return a value for a group of data. common grouping functions include: function Name function description Count return the number of records found Min return the maximum value of a numeric column or calculation column Max return the maximum value of a numeric column or calculation column Sum return a numeric column or calculation column Sum avg returns the syntax of an average grouping function for a numeric column or computed column: SELECT [column,] group_function (column ),... FROM table [WHERE condition] [group by column] [order by column]; // The total number of records returned // * Indicates a record SQL> select count (*) from emp; // returns the total number of records that comm is not empty. SQL> select count (comm) from emp; // COUNT (DISTINCT expr) returns expr Non-empty and non-repeated total records SQL> select count (distinct (sal) from emp; Note: Group functions ignore null values. // Return the average salary of all employees. SQL> select avg (nvl (sal, 0) from emp; note: the NVL function makes the grouping function unable to ignore null values. // return the employee number min SQL> select min (empno) from emp; // return the employee salary max SQL> select max (sal) from emp; // calculate the total salary issued by the company this month SQL> select sum (comm) + sum (sal) from emp; SQL> select sum (nvl (sal, 0) + nvl (comm, 0) from emp; the Group by clause Group By statement is interpreted literally as "Group by certain rules )". It is used to divide a dataset into several small areas through certain rules, and then process data for several small areas. If you want to group data by the value of a column during the query, you must use the group by clause to count the data in the group. The group by clause can be used no matter whether select uses the where clause. Note: The group by clause must be used with the grouping function; otherwise, it does not make sense. // Obtain the number of employees in each department. SQL> select deptno, count (*) as "Number of employees" from emp group by deptno; // obtain the average salary of employees in each department SQL> select deptno, avg (nvl (sal, 0) from emp group by deptno; // note: columns in the group by clause do not need to be included in the SELECT list. SQL> select avg (nvl (sal, 0) from emp group by deptno; // obtain the group by number of employees in the same position in a department, and then use SQL> select deptno, job, count (*) from emp group by deptno, job order by deptno; Invalid Group function 1. Used in the SELECT list, not included in the columns in the group Function Must be included in the group by clause. Example: SQL> select empno, count (job) from emp; the correct syntax is as follows: SQL> select empno, count (job) from emp group by empno; 2. You cannot use group functions in the WHERE clause (note ). SQL> select deptno from emp where count (job)> 0 group by deptno; Remarks: ORA-00934: the Having clause of the grouping function is not allowed to set conditions for the group by clause in a way similar to that for the WHERE clause and SELECT statements. The WHERE clause search condition is applied before grouping, while the HAVING search condition is applied after grouping. HAVING syntax is similar to WHERE syntax, but HAVING can contain aggregate functions. HAVING clause can reference any item in the selection list. Note: The having clause is usually used in conjunction with the group by clause Syntax: SELECT column, group_function FROM table [WHERE condition] [group by group_by_expression] [HAVING group_condition] [order by column]; // query the number of employees in a department greater than five Department numbers. SQL> select deptno, count (*) from emp group by deptno having count (*)> 5; Remarks: grouping functions can be nested. The output of a function can be the input of another function. The operands have an inherited execution process. However, the priority of a function is only based on the position. The function follows the principle from inside to outside, from left to right. nested functions can include nested single-row functions in group functions, or nested group functions into single-row functions or group functions. The syntax format of the entire query statement is as follows: SELECTcolumn, group_function (column) FROMtable [WHEREcondition] [GROUP BYgroup_by_expression] [HAVINGgroup_condition] [ORDER BYcolumn];