Group functions: Processing a set of data, returning a value for each set of data
Common group functions: Count () avg () max () min () sum ()
Count () function
1. Count (*): Returns the total number of rows without removing null values
2. Count (column): Returns the number of non-null rows
Select from EMP; Count (*) Count (SAL) count (COMM)------------------------------- 4
3. Except for the count (*) exception, the other group functions are to remove null
sql>Select avg (SAL), Count (Sal), sum (SAL)/count (comm) average,count (SAL)/count (NVL (comm,0)) Average2,count (comm) countnotnull from emp AVG (SAL) count (Sal) AVERAGE AVERAGE2 Countnotnull---------------------------------------------------- 2073.21429 7256.25 1 4 Here's an explanation: the divisor of AVG () is the divisor of 14,sum (SAL)/count (comm) is 4
- DISTINCT () function
Sql>SelectEmpno,ename,comm fromEMP; EMPNO ename COMM---------- -------- ----------7369SMITH7499ALLEN - 7521WARD - 7566JONES7654MARTIN1400 7698BLAKE7782CLARK7788SCOTT7839KING7844TURNER0 7876ADAMS7900JAMES7902FORD7934MILLER SQL>SelectCOUNT (Distinct comm) fromemp;--returns the number of unique non-empty Comm count (Distinctcomm)-------------------4Here's a little problem: SQL> Update empSetcomm= - whereempno=75211row updated. SQL>SelectCOUNT (Distinct comm) fromEMP; COUNT (Distinctcomm)-------------------3Description: Distinct just filtered out the repeating fields. It is not possible to say that the number of unique non-empty Comm is returned only if the Comm is non-empty and does not duplicate.
- GROUP BY clause: creating grouped data
In a SELECT statement, a column that does not use a grouping function must be in the GROUP BY clause
-
sql> Select Deptno,avg (sal) from emp;select Deptno,avg (sal) from EMP * 1th line error occurred: ? ORA-00937: Not a single group of grouping functions
Group by after columns can not appear in the SELECT statement
sql> select AVG (sal ) from the EMP Group by DEPTNO; AVG (SAL)----------1566.66667 23752916.66667
Use the GROUP by clause on multiple columns
sql> Select Deptno,job,sum ( Sal) from the EMP Group by Deptno,job; DEPTNO JOB SUM (SAL)-------------------------Clerk 1900 salesman 5600 MANAGER 2975 Clerk 950 President 1300 MANAGER 2850 Clerk Ten MANAGER 2450 ANALYST 7000 Group by Deptno first, Deptno same and then group by job
- misuse of grouping functions
1. In the SELECT statement, any columns that do not appear in the cluster function must be in the GROUP BY clause
2. You cannot qualify group functions in the WHERE clause, use the HAVING clause to qualify the grouping
Sql>SelectDeptno,avg (SAL) fromEmpwhereAVG (SAL) > -GROUP by Deptno;SelectDeptno,avg (SAL) fromEmpwhereAVG (SAL) > -GROUP BY Deptno*Section1line error: ORA-00934: The correct usage of grouping functions is not allowed here: SQL>SelectDeptno,avg (SAL) fromEMP GROUP BY DEPTNO have avg (SAL) > -; DEPTNO AVG (SAL)---------- ---------- - 2175 Ten 2916.66667
- nesting of group functions
1. Group functions can only be nested one layer
2. Using group functions to nest, you must follow the groups by clause
wrong syntax: SQL>SelectMax (avg (SAL)) fromEMP;SelectMax (avg (SAL)) fromEMP*ERROR at line1: ORA-00978: Nested group function without GROUP by error: SQL>SelectDeptno,max (avg (SAL)) fromEMP Group by DEPTNO;SelectDEPTNO, Max (avg (SAL)) fromEMP GROUP BY Deptno*ERROR at line1: ORA-00937: Not a single-Group group function because select Deptno the contents of a column, and the set of Functions Max () returns only one value is also wrong, the correct wording: SQL>SelectMax (avg (SAL)) fromEMP GROUP BY Deptno MAX (AVG (SAL))-------------2916.66667
- the order in which clauses are executed
SELECT clause FROM clause WHERE clause GROUP BY clause HAVING clauseSelectdeptno,job,sum (SAL) fromEMPwhereComm isNotNULLGROUP by Deptno,job have sum (SAL)> -order of execution of the above SQL statements1. FROM clause2The . SELECT clause3. WHERE clause?4The . GROUP BY clause5. Aggregation operation: Sum6. HAVING clause