Shulou(Shulou.com)06/01 Report--

Detailed explanation of Oracle aggregation function

The definition of an aggregate function

Aggregate function is also called group function, and in some places it is also called set function. Its data source usually comes from multiple sets of data, but it is usually returned as a set of data. The aggregate function performs calculations on a column in a set of rows and returns a single value. Aggregate functions are often used with the GROUP BY clause of SELECT statements, so they are sometimes called grouping functions. It is important to note that columns with values of null are ignored by all statistical operations except count and grouping.

Classification of biaggregate functions

1: AVG (DISTINCT | ALL)

ALL means to average all values, while DISTINCT averages only different values.

SQL > select avg (sal) from scott.emp

AVG (SAL)

-

2073.21429

SQL > select avg (distinct sal) from scott.emp

AVG (DISTINCTSAL)

-

2064.58333

SQL > select avg (all sal) from scott.emp

AVG (ALLSAL)

-

2073.21429

Note:

SQL > select avg (comm) from emp

AVG (COMM)

-

550-550=sum (comm) / 4 where 4 is the number of employees whose comm is not null

SQL > select sum (comm) / 14 from emp

SUM (COMM) / 14

-

157.142857

SQL > select avg (nvl (comm,0)) from emp

AVG (NVL (COMM,0))

-

157.142857

2: MAX (DISTINCT | ALL)

To find the maximum value, ALL means to find the maximum value for all values, DISTINCT means to find the maximum value for different values, and only take the same value once.

SQL > select max (sal) from emp

MAX (SAL)

-

5000

SQL > select max (all sal) from emp

MAX (ALLSAL)

-

5000

SQL > select max (distinct sal) from emp

MAX (DISTINCTSAL)

-

5000

SQL > select max (hiredate) from emp

MAX (HIRED

-

23-MAY-87

3: MIN (DISTINCT | ALL)

To find the minimum, ALL means to minimize all values, DISTINCT means to minimize different values, and only take the same value once.

SQL > select min (sal) from emp

MIN (SAL)

-

eight hundred

SQL > select min (all sal) from emp

MIN (ALLSAL)

-

eight hundred

SQL > select min (distinct sal) from emp

MIN (DISTINCTSAL)

-

eight hundred

SQL > select min (hiredate), min (to_char (sysdate,'yyyy-mm-dd hh34:mi:ss')) from emp

MIN (HIRED MIN (TO_CHAR (SYSDATE)

--

17-DEC-80 2014-08-23 22:10:49

4: STDDEV (distinct | all)

Calculate the standard deviation. ALL means to find the standard deviation for all values, and DISTINCT means to calculate the standard deviation only for different values.

SQL > select stddev (sal) from emp

STDDEV (SAL)

-

1182.50322

SQL > select stddev (all sal) from emp

STDDEV (ALLSAL)

-

1182.50322

SQL > select stddev (distinct sal) from emp

STDDEV (DISTINCTSAL)

-

1229.95096

5: VARIANCE (DISTINCT | ALL)

Finding covariance ALL means finding covariance for all values, and DISTINCT means finding covariance only for different values

SQL > select variance (sal) from emp

VARIANCE (SAL)

-

1398313.87

SQL > select variance (all sal) from emp

VARIANCE (ALLSAL)

-

1398313.87

SQL > select variance (distinct sal) from emp

VARIANCE (DISTINCTSAL)

-

1512779.36

6: SUM (DISTINCT | ALL)

Summing ALL means summing all values, while DISTINCT means summing only different values (the same value is taken only once)

SQL > select sum (sal) from emp

SUM (SAL)

-

29025

SQL > select sum (all sal) from emp

SUM (ALLSAL)

-

29025

SQL > select sum (distinct sal) from emp

SUM (DISTINCTSAL)

-

24775

7:COUNT (DISTINCT | ALL)

Ask for the number of records and data. ALL makes statistics on all records and arrays, while DISTINCT only counts different values (the same value is taken only once)

SQL > select count (sal) from emp

COUNT (SAL)

-

fourteen

SQL > select count (all sal) from emp

COUNT (ALLSAL)

-

fourteen

SQL > select count (distinct sal) from emp

COUNT (DISTINCTSAL)

-

twelve

8: MEDIAN

Find the median

SQL > select median (sal) from emp

MEDIAN (SAL)

-

1550

SQL > select median (all sal) from emp

MEDIAN (ALLSAL)

-

1550

SQL > select median (distinct sal) from emp

Select median (distinct sal) from emp

*

ERROR at line 1:

ORA-30482: DISTINCT option not allowed for this function-- error: the DISTINCT option is disabled in this function.

Three Group by clause

Group By sentence, literally understood in English, means "Group according to (by) certain rules". Its function is to divide a data set into several small regions through certain rules, and then process the data for several small regions. If you need to group by the value of a column during the query to count the data within that group, use the group by clause. You can use the group by clause regardless of whether select uses the where clause or not.

Note: the group by clause must be used in conjunction with the grouping function, otherwise it is meaningless

1 find out the number of people in each department

SQL > select deptno,count (*) num from emp group by deptno order by deptno

DEPTNO NUM

--

10 3

20 5

30 6

2 average salary of employees in each department

SQL > select deptno,avg (sal) from emp group by deptno

DEPTNO AVG (SAL)

--

30 1566.66667

20 2175

10 2916.66667

SQL > select deptno,avg (nvl (sal,0)) from emp group by deptno

DEPTNO AVG (NVL (SAL,0))

--

30 1566.66667

20 2175

10 2916.66667

3 salary + bonus of employees in each department

SQL > select deptno,avg (sal+nvl (comm,0)) from emp group by deptno

DEPTNO AVG (SAL+NVL (COMM,0))

--

30 1933.33333

20 2175

10 2916.66667

SQL > select deptno,avg (nvl (sal,0) + nvl (comm,0)) from emp group by deptno

DEPTNO AVG (NVL (SAL,0) + NVL (COMM,0))

30 1933.33333

20 2175

10 2916.66667

Note: columns in the group by clause do not have to be included in the SELECT list

4 after finding out the number of employees in the same position in a department, group by can be grouped with multiple fields.

SQL > select deptno,job,count (*) from emp group by deptno,job order by deptno

DEPTNO JOB COUNT (*)

10 CLERK 1

10 MANAGER 1

10 PRESIDENT 1

20 ANALYST 2

20 CLERK 2

20 MANAGER 1

30 CLERK 1

30 MANAGER 1

30 SALESMAN 4

9 rows selected.

5 illegal use of group functions

(1) columns that are included in the SELECT list but not in the group function must be included in the GROUP BY clause.

For example:

SQL > select deptno,count (job) from emp

Select deptno,count (job) from emp

*

ERROR at line 1:

ORA-00937: not a single-group group function

The correct writing is as follows:

SQL > select deptno,count (job) from emp group by deptno

DEPTNO COUNT (JOB)

--

30 6

20 5

10 3

(2) Group functions cannot be used in WHERE clauses (note).

SQL > select deptno from emp where count (job) > 0 group by deptno

Remarks ERROR at line 1: ORA-00933: SQL command not properly ended

Grouping functions are not allowed here

(3) Having clause

The HAVING clause sets conditions on the GROUP BY clause in a similar way to the interaction between the WHERE clause and the SELECT statement. The WHERE clause search condition is applied before the grouping operation, while the HAVING search condition is applied after the grouping operation. The HAVING syntax is similar to the WHERE syntax, but HAVING can contain aggregate functions. The HAVING clause can refer to any item that appears in the selection list.

Note: having clause is usually used in conjunction with 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 departments with more than 3 employees

SQL > select deptno,count (*) from emp group by deptno having count (*) > 3 order by deptno

DEPTNO COUNT (*)

--

20 5

30 6

Note:

Aliases are not allowed after 1 group by

SQL > select deptno d sal sum (sal) from emp group by deptno

D SUM (SAL)

--

30 9400

20 10875

10 8750

SQL > select deptno d sal sum (sal) from emp group by d

Select deptno djm sum (sal) from emp group by d

*

ERROR at line 1:

ORA-00904: "D": invalid identifier

Cannot receive digits after 2 group by

SQL > select job,sum (sal) from emp group by 1

Select job,sum (sal) from emp group by 1

*

ERROR at line 1:

ORA-00979: not a GROUP BY expression

SQL > select job,sum (sal) from emp group by job

JOB SUM (SAL)

--

CLERK 4150

SALESMAN 5600

PRESIDENT 5000

MANAGER 8275

ANALYST 6000

Columns that are not available after 3 group by can be connected to select.

SQL > select sum (sal) from emp group by deptno

SUM (SAL)

-

9400

10875

8750

Columns that appear after 4 select must all appear after group by

SQL > select job,deptno,sum (sal) from emp group by job,deptno

JOB DEPTNO SUM (SAL)

MANAGER 20 2975

PRESIDENT 10 5000

CLERK 10 1300

SALESMAN 30 5600

ANALYST 20 6000

MANAGER 30 2850

MANAGER 10 2450

CLERK 30 950

CLERK 20 1900

9 rows selected.

SQL > select job,deptno,sum (sal) from emp group by job

Select job,deptno,sum (sal) from emp group by job

*

ERROR at line 1:

ORA-00979: not a GROUP BY expression

SQL > select job,deptno,sum (sal) from emp group by deptno

Select job,deptno,sum (sal) from emp group by deptno

*

ERROR at line 1:

ORA-00979: not a GROUP BY expression

Where cannot be used after 4 group by, because where works before grouping, and the data after grouping needs to be filtered using having

SQL > select deptno,avg (sal) from emp group by deptno where deptno > 10

Select deptno,avg (sal) from emp group by deptno where deptno > 10

*

ERROR at line 1:

ORA-00933: SQL command not properly ended

SQL > select deptno,avg (sal) from emp group by deptno having deptno > 10

DEPTNO AVG (SAL)

--

30 1566.66667

20 2175

Before 5 group by, you can use where to filter data

A:

SQL > select deptno,sum (sal) from emp where deptnotations 30 group by deptno having sum (sal) > 5000

DEPTNO SUM (SAL)

--

20 10875

10 8750

B:

SQL > select deptno,sum (sal) from emp group by deptno having sum (sal) > 5000 and deptnotations 30

DEPTNO SUM (SAL)

--

20 10875

10 8750

Performance: can filter data in where, not in having, An and B can achieve the same goal, but A performance is relatively better, because A now filters out deptno=30 data and then groups the filtered data into temporary tablespaces. On the other hand, B reads all the data into the temporary table space, and then filters the data in the temporary table space, so B needs larger temporary table space for group filtering, and the index performance is poor.

Reference and-http://www.2cto.com/database/201305/214634.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.