Chapter 05Reporting Aggregated data Using the Group Functions 02

Using the GROUP BY Clause

The GROUP BY column does not have to be in the SELECT list.(不过这样一般意义不大)

SQL> SELECT AVG(salary) AvgSalary FROM employees GROUP BY department_id ORDER BY AVGSALARY;

 AVGSALARY
----------
3475.55556
      4150
      4400
      5760
      6500
      7000
8601.33333
8955.88235
      9500
     10000
     10154

 AVGSALARY
----------
19333.3333

12 rows selected.

Grouping by More than One Column

Using the GROUP BY Clause on Multiple Columns

SQL> SELECT department_id dept_id,job_id,SUM(salary) FROM employees GROUP BY department_id,job_id ORDER BY dept_id;

   DEPT_ID JOB_ID     SUM(SALARY)
---------- ---------- -----------
        10 AD_ASST           4400
        20 MK_MAN           13000
        20 MK_REP            6000
        30 PU_CLERK         13900
        30 PU_MAN           11000
        40 HR_REP            6500
        50 SH_CLERK         64300
        50 ST_CLERK         55700
        50 ST_MAN           36400
        60 IT_PROG          28800
        70 PR_REP           10000

   DEPT_ID JOB_ID     SUM(SALARY)
---------- ---------- -----------
        80 SA_MAN           61000
        80 SA_REP          243500
        90 AD_PRES          24000
        90 AD_VP            34000
       100 FI_ACCOUNT       39600
       100 FI_MGR           12008
       110 AC_ACCOUNT        8300
       110 AC_MGR           12008
           SA_REP            7000

20 rows selected.

lllegal Queries Using Group Functions

• Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY clause:

SQL> SELECT department_id,count(last_name) FROM employees;
SELECT department_id,count(last_name) FROM employees
       *
ERROR at line 1:
ORA-00937: not a single-group group function

Solution:A GROUP BY clause must be added to count the last names for each department_id.

SQL> SELECT department_id,job_id,count(last_name) FROM employees GROUP BY department_id;
SELECT department_id,job_id,count(last_name) FROM employees GROUP BY department_id
                     *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

Solution:Either add job_id in the GROUP BY or remove the job_id column from the SELECT list.

You cannot use the WHERE clause to restrict groups.

You use the HAVING clause to restrict groups.

You cannot use group functions in the WHERE clause.

SQL> SELECT department_id,AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id;
SELECT department_id,AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id
                                                      *
ERROR at line 1:
ORA-00934: group function is not allowed here

分析原因：在上面的SQL中，Oracle server的查询执行顺序是：首先执行WHERE AVG(salary)，然后执行GROUP BY department_id，再执行SELECT department_id,AVG(salary)

Solution:

SQL> SELECT department_id,AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 8000 ORDER BY department_id;

DEPARTMENT_ID AVG(SALARY)
------------- -----------
           20        9500
           70       10000
           80  8955.88235
           90  19333.3333
          100  8601.33333
          110       10154

6 rows selected.

Restricting Group Results with the HAVING Clause

When you use the HAVING clause,the Oracle server restricts groups as follows:

1、Rows are grouped.

2、The group function is applied.

3、Groups matching the HAVING clause are displayed.

Using the HAVING Clause

SQL> SELECT department_id,MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary) > 8000 ORDER BY department_id;

DEPARTMENT_ID MAX(SALARY)
------------- -----------
           20       13000
           30       11000
           50        8200
           60        9000
           70       10000
           80       14000
           90       24000
          100       12008
          110       12008

9 rows selected.

SQL> SELECT job_id,SUM(salary) PAYROLL FROM employees WHERE job_id NOT LIKE '%REP%' GROUP BY job_id HAVING SUM(salary)>13000 ORDER BY SUM(salary);

JOB_ID        PAYROLL
---------- ----------
PU_CLERK        13900
AD_PRES         24000
IT_PROG         28800
AD_VP           34000
ST_MAN          36400
FI_ACCOUNT      39600
ST_CLERK        55700
SA_MAN          61000
SH_CLERK        64300

9 rows selected.

Oracle Server SQL执行顺序图解:

Nesting Group Functioins

Display the maximum average salary:

SQL> SELECT MAX(AVG(salary)) MAZ_SALARY,MIN(AVG(salary)) FROM employees GROUP BY department_id;

MAZ_SALARY MIN(AVG(SALARY))
---------- ----------------
19333.3333       3475.55556

Summary

In this lesson,you should have learned how to:

• Use the group functions COUNT,MAX,MIN,SUM,and AVG
• Write queries that use the GROUP BY clause
• Write queries that use the HAVING clause

SELECT column,group_fuction

FROM table

[WHERE conditions]

[GROUP BY group_by_expression]

[HAVING group_condtion]

[ORDER BY column]

注意:精通上述6大子句的执行顺序