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

Source: Internet
Author: User

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

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

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

       

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.