ORACLE SQL Group Functions "Weber products must be a boutique"

2. Group functions: Processing a set of data, returning a value for each set of data

4. Common group functions: Count () avg () max () min () sum ()

5. 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

6. 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.  

8. 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

10.  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   

       

11. 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

12. 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

13. 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