We have introduced a lotOracleInOracleThere is another type of function, that is, aggregate function,OracleAggregate functions in are very useful, mainly used for statistics, averages and other work, you must remember.
First, let's briefly introduce several commonOracle.
Oracle aggregate function name |
Functions |
Count |
Used to calculate the number of valid data |
Max |
Used to find the largest data in the given data |
Min |
Used to find the smallest data in the given data |
AVG |
Used to calculate the average value of a given data |
Sum |
Used to calculate the sum of given data |
Variance |
Used to calculate the standard deviation of the given data |
Stddev |
Used to calculate the variance of the given data |
First, there are some examples and precautions.
1.For Aggregate functions, if a null value exists in the given value,OracleWill be ignored directly.
For example,ForScottOfSchemaTables inEMP;
Select count (*) from EMP;
The result is as follows:
Count (*)
----
14
However, we noticed thatMgrOne column in the column is null, so
Select count (MGR) from EMP;
The following result is displayed.
Select count (MGR)
----
13
That is to say,OracleThe null value is ignored.
Of courseSum, Max, Min, AVGAnd other Aggregate functions.
For exampleSelect sum (Comm) from EMP; part of NULL data is automatically ignored.
2.For Aggregate functionsDistinctKeyword to compress duplicate values.
For example, if we want to count the total number of departments
If we write
Select count (deptno) from EMP;
The error message is returned. Because many duplicate values are actually included. To find the correct answer, you should write it like this.
Select count (distinct deptno) from EMP;
3.Separate useOracleAggregate functions in It may be used for a limited number of purposes. More often, Aggregate functions andGroupKeyword. We will introduce this point later.