Oracle Grouping and Group functions

One, sum and seek average

1. What is a group function

The group function is the multiline function that we talked about earlier. It operates primarily on tables. Because there are only a few rows of data in the table. Let's take the two group functions, SUM and AVG, for example, to start by introducing the attributes of the group functions.

2. Sum Sum function

Gyj@ocm> Select sum (Salary) from T1;

SUM (SALARY)

-----------

315888

When Oracle executes the sum () function, it extracts each row from the salary column and accumulates the results together. When we talk about single-line functions and multi-line functions, we say that for single-line functions, each row of data in a column is considered an individual, and a single function evaluates each individual individually. The multi-line function, however, regards all the row data in the column as a whole, and the multiline function sums up all the elements in the whole, the average, the maximum or the smallest, and so on, no matter how many rows are included in the whole, the result is only one. A total sum or average value, or maximum, or minimum value, and so on. Single-line functions handle each row separately, and each row returns a result.

Gyj@ocm> Select Id,sum (Salary) from T1;

Select Id,sum (Salary) from T1

*

ERROR at line 1:

Ora-00937:not a single-group group function

It is easy to understand that the ID (number) column has 10 rows of data, and sum (salary) has only one row of results. A 10 line, one row, Oracle can't combine these two columns together. A column can be used in combination with a single line function, because each row of functions returns a result with 10 rows in the column, returning 10 results. Such as:

Gyj@ocm> Select Id,round (salary,1) from T1;

ID ROUND (salary,1)

---------- ---------------

2 8000

3 10000

4 15000

5 0

7 12000

6 12000

1 12000

3. WHERE Condition and composition function

When there is no condition, the composite function defaults to the processing of all row data in the column. We can use conditions to limit the number of rows to sum. For example, now I just want to ask for the sum of the monthly salary of Joe's name.

Gyj@ocm> Select sum (Salary) from T1 where name= ' Joe ';

SUM (SALARY)

-----------

22000