Aggregate functions such as Count, Max, Min, sum, and AVG in Oracle

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.