Oracle grouping and grouping Functions

Oracle grouping and grouping functions 1. summation and averaging 1. What is a group function is the multi-row function we mentioned earlier. It is mainly used for table operations. Because only tables have multiple rows of data. The following uses the SUM and AVG functions as examples to describe the features of group functions. Www.2cto.com 2. SUM sum function gyj @ OCM> select SUM (salary) from t1; SUM (SALARY) ----------- 315888 when ORACLE executes the SUM () function, each row is extracted from the SALARY column and the results are accumulated together. When talking about single-row functions and multi-row functions, for single-row functions, each row of data in the column is treated as one entity, and the single-row functions calculate each individual. The multiline function regards all row data in the column as a whole. The multiline function performs operations such as summation, averaging, and maximum or minimum statistics on all elements in the whole column, no matter how many rows are included, only one result is returned. A sum or average value, a maximum value, or a minimum value. A single row function processes 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 function www.2cto.com is easy to understand. The id (number) column has 10 rows of data, while SUM (salary) has only one row of results. Oracle cannot combine these two columns with one row and one row. A column can be used in combination with a single row function, because each row of a single row function returns a result. If there are 10 rows in the column, 10 results are returned. For example, 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 120003. When no conditions exist for the WHERE condition and composite function, the Composite Function processes all row data in the column by default. We can use conditions to limit the number of rows for summation. For example, I only want to calculate the total monthly salary of Joe. Gyj @ OCM> select sum (salary) from t1 where name = 'job'; SUM (SALARY) ----------- 220004. The AVG and SUM values of VAG are used exactly the same, after SUM is obtained, it returns an average value by dividing by the number of rows. For example, gyj @ OCM> select avg (salary) from t1; AVG (SALARY) ----------- 17549.3333 5. the distinct function of DISTINCTDISTINCT is to remove duplicate values, which we described in the previous lesson. It can also be used in group functions. Let's take a look at the effect: gyj @ OCM> select sum (distinct salary) from t1; SUM (DISTINCTSALARY) ----------------- 1628886, The Impact of NULL values first, for the SUM () function, adding a NULL value does not have any effect. The SUM () function only ignores NULL values. Gyj @ OCM> insert into t1 values (26, 'Rose ', null, null); 1 row created. gyj @ OCM> commit; Commit complete. gyj @ OCM> select sum (salary) from t1; SUM (SALARY) --------- 315888 the result is still 315888. Before adding a NULL value, the sum is 315888. For an average function, the sum is first obtained and then divided by the number of rows. Therefore, the NULL value should not be calculated within the number. That is to say, there are 22 rows in the salary column, of which 4 are NULL. The result of the sum is 315888. So, when we calculate the average, we divide the sum by 315888 by 22, or we should divide it by 18. By default, it is divided by 18. That is to say, in ORACLE group functions, NULL values are usually skipped and ignored. Let's take a look at the result of Averaging: gyj @ OCM> select avg (salary) from t1; AVG (SALARY) ----------- 17549.3333 to see if the result is 315888 divided by 18: gyj @ OCM> select 315888/18 from dual; 315888/18 ---------- 17549.3333 indeed, it is 315888 divided by 18. The null value is simply ignored, as if it has never been used, but sometimes we cannot simply ignore null. A null value is also a row of data. It may have just been added, but it has not yet produced performance data, but it is also a row of data. Sometimes it cannot be removed from the row. At this time, the NVL () function will be used. First use the NVL () function to convert the null value to 0, and then calculate the group function. The null value will not be ignored. The usage form is as follows: gyj @ OCM> select avg (nvl (salary, 0) from t1; AVG (NVL (SALARY, 0) ---------------- nested functions, as we have mentioned earlier. It also processes the functions in the layer and transmits the processing results to the outer layer for processing. When a null value is encountered, it is first converted from NVL to 0, and then AVG () is used to include 0 in the calculation of the average. Null is ignored by AVG, and 0 is not ignored. Next, let's verify: gyj @ OCM> select 315888/22 from dual; 315888/22 ---------- 14358.5455315888 divided by 22, and the result is 14358.5455. NULL values are not ignored. 2. The use of the MAX and MIN functions is very simple on the basis of understanding the first two functions. Gyj @ OCM> select max (salary), min (salary) from t1; MAX (SALARY) MIN (SALARY) ----------- --------- 30000 the maximum value in the 0SALARY column is 30000, and the minimum value is 0. NULL is not counted. All group functions ignore NULL values. Of course, we can use NVL to convert null values to non-empty values: gyj @ OCM> select max (salary), min (nvl (salary, 0) from t1; MAX (SALARY) MIN (NVL (SALARY, 0) ----------- ---------------- 30000 0 3. COUNT the number of rows in a column that meet the condition. For example, gyj @ OCM> select count (salary) from t1 where salary> 5000; COUNT (SALARY) ----------- 17 counts the number of rows where the SALARY column is greater than 5000. The salary column in the COUNT (salary) brackets can be changed to *: gyj @ OCM> select count (*) from t1 where salary> 5000; COUNT (*) ---------- 17 after, is the number of rows that meet the salary> 5000 condition in Table t1. This is similar to COUNT (salary. The difference is that the following conditions are removed: gyj @ OCM> select count (*) from t1; COUNT (*) ---------- 22, which counts the total number of rows in Table t1. * Indicates that the statistics are for all columns. If you replace COUNT (*) with COUNT (salary), as follows: gyj @ OCM> select count (salary) from t1; COUNT (SALARY) ------------- 18 this will only count the number of rows in the SALARY column. Like other functions, COUNT ignores NULL values. In COUNT, you can also use DISTINCT to remove duplicate values and count only the number of non-duplicate rows: gyj @ OCM> select COUNT (distinct salary) from t1; COUNT (DISTINCTSALARY) --------------------- 11 in the salary column, there are indeed only 11 values. Of course, null values are not included, but NVL can be used to convert null values to 0. Iv. Grouping and GROUP functions 1. Syntax of grouping group by: group by column name 1, column name 2 ,...... Grouping is used to divide rows with the same column values into groups based on the values of some columns. This is the group. Grouping and grouping functions can be combined to provide very powerful functions. The group function is represented by its name. Is it a group function. Let's take a look at the t3 test table: gyj @ OCM> select * from t3; id name salary ---------- 1 gyj1 5000 1 gyj11 5000 2 gyj2 6000 2 gyj22 6000 3 gyj3 7000 3 gyj33 7000 4 gyj4 8000 4 gyj44 8000gyj @ OCM> select id, sum (salary) from t3 group by id; id sum (SALARY) ---------- ----------- 1 10000 2 12000 4 16000 3 14000 group the id column of Table t3 first, and then combine the last two into one, merge two values into one, two three into one, and two four into one. All repeated values are merged into one. While grouping, SUM (SALARY) is used to calculate the total SALARY of each group. We can use all group functions in grouping to separately sum, calculate average, maximum, and minimum values for the data in each group, and calculate the number of rows: gyj @ OCM> select id, sum (salary), avg (salary), max (salary), min (salary), count (*) from t3 group by id; id sum (SALARY) AVG (SALARY) MAX (SALARY) MIN (SALARY) COUNT (*) ---------- ----------- ------------- ------------ 1 10000 5000 5000 5000 2 2 12000 6000 6000 2 4 6000 16000 8000 2 3 8000 8000 2, I used every group of functions we mentioned. After grouping, non-grouping Columns cannot be directly displayed, for example, gyj @ OCM> select id, salary from t3 group by id; select id, salary from t3 group by id * ERROR at line 1: ORA-00979: not a group by expression grouping after the ID is merged into four rows, the row values are 1, 2, 3, 4, the SALARY column still has eight rows of values. In this case, ORACLE cannot combine these two columns. Therefore, an error is reported. After grouping, only group columns can appear in the column name table between SELECT and FROME. To create other columns, you must use the group function to process the row values in the column. In the example above, SALARY has eight rows and must use the group function, process eight rows into four rows. Gyj @ OCM> select id, salary from t3 group by id, salary; id salary ---------- 1 5000 2 6000 3 7000 4 80002. In Group by, use the function gyj @ OCM> select name from t3 group by substr (name ); select name from t3 group by substr (name,) * ERROR at line 1: ORA-00979: not a group by expression this command reports an ERROR, but ORACLE has pointed out that, the error is not in our group clause. The Group clause is correct and functions can be used in the group. Extract the first four characters of the NAME and group the four characters. Oracle can implement this function. The column NAME after SELECT is incorrect. There are only four values after grouping, but there are eight values for NAME. The correct format is: gyj @ OCM> select substr (name,), count (*) from t3 group by substr (name,); SUBSTR (n count (*) -------- ---------- gyj4 2gyj2 2gyj1 2gyj3 2 I use COUNT (*) to calculate the number of rows in each group. The column name after SELECT, except for GROUP functions, must have the same content as the GROUP expression of group !!! 3. conditions can be used to restrict rows in a group. First, you can use WHERE to filter out rows that do not meet the conditions and group rows that meet the conditions. Second, use HAVING After grouping to display only the grouping results that meet the conditions. Let's talk about WHERE first. (1) WHERE condition I want to group employees with salaries greater than 5000 by ID gyj @ OCM> select id, count (*) from t3 where salary> 5000 group by id; id count (*) ---------- 2 2 2 4 2 3 2 is no different from a common GROUP, but a condition is added before group. Note: The condition judgment in WHERE takes precedence over Grouping execution. First, judge the condition and group the rows that meet the condition. (2) HAVING condition HAVING indicates that only the rows meeting the condition are displayed after the group. In the preceding example, for example, After grouping, only the information with the id greater than is queried: gyj @ OCM> select id, count (*) from t3 where salary> 5000 group by id having id> 2; the condition after id count (*) -------------- 4 2 3 2HAVING cannot be moved to WHERE, because when executing WHERE, ORACLE has not started grouping. When the group function SUM is executed, an error is returned. The use of HAVING is also limited. HAVING is executed after grouping. After grouping, all columns in the original table except the group column cannot be used directly. Gyj @ OCM> select id, count (*) from t3 group by id having salary> 5000; select id, count (*) from t3 group by id having salary> 5000 * ERROR at line 1: ORA-00979: not a group by expression I moved the above WHERE condition salary> 5000 to HAVING, an error is reported. After HAVING, and SEELCT... Like this column, the FROM column can only have group columns, and other columns processed by the group function. 4. the alias gyj @ OCM> select id, count (*) from t3 where salary> 5000 group by 1; select id, count (*) cannot be used in group (*) from t3 where salary> 5000 group by 1 * ERROR at line 1: ORA-00979: not a group by expressionid In the first column, grouped by id, in group by, I use 1 to group with the id of the first column. This method cannot be used different from order !!! Gyj @ OCM> select id bh, count (*) from t3 where salary> 5000 group by bh; select id bh, count (*) from t3 where salary> 5000 group by bh * ERROR at line 1: ORA-00904: "BH": invalid identifier I set the ID column to alias bh, then, an error occurs in gyj @ OCM> select id bh, count (*) from t3 where salary> 5000 group by id; bh COUNT (*) ---------- 2 2 2 2 3 2 I set the ID column as the alias "bh" and then use the id group. Please note this! 5. The nested single-row functions and group functions of group functions can be nested, and the group functions and group functions can also be nested. For example, gyj @ OCM> select sum (salary) from t3 group by id; SUM (SALARY) --------- 10000 12000 16000 14000 I group tables by id, calculate the total salary of each group. Now I want to find out which group has the highest total salary: gyj @ OCM> select max (sum (SALARY) from t3 group by id; MAX (SUM (salary )) ---------------- 16000 this is the nested use of group functions. ORACLE stipulates that group function nesting can only be embedded in two layers. In fact, multi-layer nesting has no practical purpose, so ORACLE does not provide multi-layer nesting of group functions. However, single-row functions can be nested in multiple layers.