Chapter 05-Reporting Aggregated data Using the Group Functions - 01

Objectives

After completing this lesson,you should be able to do the following:

• Identify the available group functions
• Describe the use of group functions
• Group data by using the GROUP BY clause
• Include or exclude grouped rows by using the HAVING clause

Lesson Agenda

• Groups functions:
  • -Types and syntax
  • -Use AVG,SUM,MIN,MAX,COUNT
  • -Use DISTINCT keyword within group of functions
  • -NULL values in a group function
• Grouping rows:
  • -GROUP BY clause
  • -HAVING clause
• Nesting group functions 

What are group functions?

Group functions operate on sets of rows to give one result per group.

Types of Group Functions

• AVG
• COUNT
• MAX
• MIN
• STDDEV(计算标准偏差)
• SUM
• VARIANCE(计算方差)

Group Functions:Syntax

SELECT group_function(column),...

FROM table

[WHERE condition]

[ORDER BY column]

注意要点：

　　(1)、组函数不包含NULL值，如果遇到NULL值，会主动放弃掉NULL值部分;如果想将NULL值作为计算元素在内，可以使用NVL函数，进行转换一下.

　　(2)、如果组函数操作的column中，存在重复行，可以使用keyword DISTICT,过滤掉重复行；缺省情况下Group Function是按照ALL来进行计算的.

Using the AVG and SUM Functions

You can use AVG and SUM for numeric data

SQL> SELECT AVG(salary),MAX(salary),MIN(SALARY),SUM(SALARY) FROM employees WHERE job_id LIKE '%REP%';

AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)
----------- ----------- ----------- -----------
 8272.72727       11500        6000      273000

Using the MIN and MAX Functions

You can use MIN and MAX for numeric,character,and date data types.

SQL> SELECT MIN(hire_date),MAX(hire_date) FROM employees;

MIN(HIRE_ MAX(HIRE_
--------- ---------
13-JAN-01 21-APR-08

SQL> SELECT MIN(last_name),MAX(last_name) FROM employees;

MIN(LAST_NAME)            MAX(LAST_NAME)
------------------------- -------------------------
Abel                      Zlotkey

SQL> SELECT MIN(salary),MAX(salary) FROM employees;

MIN(SALARY) MAX(SALARY)
----------- -----------
       2100       24000

Using the COUNT Function

• COUNT(*) return the number of rows in a table:

SQL> SELECT COUNT(*) FROM employees;

  COUNT(*)
----------
       107

• COUNT(expr) returns the number of rows with non-null values for expr:

SQL> SELECT count(commission_pct) FROM employees  WHERE department_id = 80;

COUNT(COMMISSION_PCT)
---------------------
                   34

SQL> SELECT COUNT(commission_pct) FROM employees;

COUNT(COMMISSION_PCT)
---------------------
                   35
SQL> SELECT COUNT(NVL(commission_pct,0)) FROM employees;

COUNT(NVL(COMMISSION_PCT,0))
----------------------------
                         107

Using the DISTINCT Keyword

• COUNT(DISTINCT expr) returns the number of distict non-null values of expr.
• TO display the number of distinct department values in ther EMPLOYEES table:

SQL> SELECT COUNT(department_id) FROM employees;

COUNT(DEPARTMENT_ID)
--------------------
                 106

SQL> SELECT COUNT(DISTINCT department_id) FROM employees;

COUNT(DISTINCTDEPARTMENT_ID)
----------------------------
                          11

SQL> SELECT COUNT(ALL department_id) FROM employees;

COUNT(ALLDEPARTMENT_ID)
-----------------------
                    106

SQL> SELECT COUNT(NVL(department_id,0)) FROM employees;

COUNT(NVL(DEPARTMENT_ID,0))
---------------------------
                        107

Group Functions and Null Values

• Group functions ignore null values in the column:

SQL> SELECT AVG(commission_pct) FROM employees;

AVG(COMMISSION_PCT)
-------------------
         .222857143

• The NVL function forces group functions to include null values:

SQL> SELECT AVG(NVL(commission_pct,0)) FROM employees;

AVG(NVL(COMMISSION_PCT,0))
--------------------------
                .072897196

Creating Groups of Data

Creating Groups of Data、GROUP BY Clause Syntax:

SELECT column,group_function(column)

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[ORDER BY column]
注意：
(1)、group_function(column)与group_by_expression是有关联的.怎么个关联法?
(2)、column一定要包含在group by clause
(3)、如果where clause和group by clause同时存在的话，oracle server一定是先执行where clause.
(4)、在where clause和group by clause中是不能使用alias的.

You can divide rows in a table into smaller groups by using the GROUP BY clause.

Using the GROUP BY Clause

All columns in the SELECT list that are not in group functions must be in the GROUP BY clause

SQL> SELECT NVL(department_id,999) deptid,ROUND(AVG(salary),2) AvgSalary FROM employees GROUP BY department_id ORDER BY department_id;

    DEPTID  AVGSALARY
---------- ----------
        10       4400
        20       9500
        30       4150
        40       6500
        50    3475.56
        60       5760
        70      10000
        80    8955.88
        90   19333.33
       100    8601.33
       110      10154

    DEPTID  AVGSALARY
---------- ----------
       999       7000

12 rows selected.