求一SQL 分组语句！

[DiligenceMan]

表结构数据如下：
SQL> desc emp;
Name Type Nullable Default Comments
------- ------------ -------- ------- --------
ENAME VARCHAR2(20) Y
EMPNO VARCHAR2(20) Y
SAL NUMBER(6,2) Y
DEPTNO NUMBER(2) Y
JOB VARCHAR2(20) Y
COMM NUMBER(6,2) Y
EMPDATE DATE Y

SQL> select * from emp;

ENAME EMPNO SAL DEPTNO JOB COMM EMPDATE
-------------------- -------------------- -------- ------ -------------------- -------- -----------
HES 30 2700.45 30 MANAGER 500.60 2008-8-26 2
HES 30 3700.45 30 MANAGER 500.60 2008-8-26 2
scott 7788 2000.00 2 560.00 2008-8-26 2
JOHN 1587 1000.00 30 1987-3-5
HES 30 2970.50 3 MANAGER 500.60 2008-8-26 2
MARTIN 30 1617.50 3 MANAGER 500.60 2008-8-26 2
MARTIN 30 1617.50 3 MANAGER 500.60 2008-8-26 2
tom 0101 3328.00 3 PM 600.60 1982-5-1
SMITH 0112 4400.50 3 PG 600.60 2008-8-26 2
PETER 0105 4400.50 3 PG 600.60 2008-8-26 2
JACK 0103 4400.50 3 PG 600.60 2008-8-26 2
PAUL 0104 4400.50 3 PG 600.60 2008-8-26 2
gao 0102 1128.00 3 PP 600.60 2008-8-26 2
CLERK 0106 1500.00 30 WY 300.00 2008-8-26 2
ALLEN 1017 1425.45 30 WY 100.60 2008-8-26 2
ALLEN 1017 1425.45 30 WY 100.60 2008-8-26 2
WARD 0017 1470.45 30 WY 500.60 2008-8-26 2
MARTIN 0011 1470.45 30 WY 500.60 2008-8-26 2

18 rows selected

SQL>


问题如下：
求 每个部门每个岗位的平均工资，每个部门的平均工资，每个岗位的平均工资。
这3个平均工资，给我弄的有点糊涂，不知道分组之后的sql怎么写了！

在线等，谢谢！

[caoleione]

select decode(grouping(depno),1,'ALL DEPNO',depno) as depno,
decode(grouping(job),1,'ALL JOP',job) as jop, avg(sal)
from emp
group by cube(depno,job)

[bugchen888]

SELECT deptno, job, AVG(sal)
FROM emp
GROUP BY rollup(deptno, job)
/

[lovewds2002]

按区分组:
SELECT DISTINCT DEPTNO,
JOB,
AVG(SAL) OVER(PARTITION BY DEPTNO, JOB),
AVG(SAL) OVER(PARTITION BY DEPTNO),
AVG(SAL) OVER(PARTITION BY JOB)
FROM EMP ;

[hebo2005]

如果不要求一条sql出来三个结果

可以这样
每个部门每个岗位的平均工资
select depno,job, avg(sal)
from emp
group by depno,job

每个部门
select depno, avg(sal)
from emp
group by depno


每个岗位的平均工资。
select job, avg(sal)
from emp
group by job

[oracledbalgtu]

SELECT DISTINCT DEPTNO,
       JOB,
       AVG(SAL) OVER(PARTITION BY DEPTNO, JOB),
       AVG(SAL) OVER(PARTITION BY DEPTNO),
       AVG(SAL) OVER(PARTITION BY JOB)
  FROM scott.EMP ;

[Quote=引用楼主 DiligenceMan 的帖子:]
表结构数据如下：
SQL> desc emp;
Name Type Nullable Default Comments
------- ------------ -------- ------- --------
ENAME VARCHAR2(20) Y
EMPNO VARCHAR2(20) Y
SAL NUMBER(6,2) Y
DEPTNO NUMBER(2) Y
JOB VARCHAR2(20) Y
COMM NUMBER(6,2) Y …
[/Quote]

[mantisXF]

TRY IT ..

SQL> SELECT * FROM EMP;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10

14 rows selected

-- 每个部门每个岗位的平均工资
SQL> SELECT DECODE(GROUPING(DEPTNO),1,'--TOTAL--',DEPTNO) "AVG_DEPTNO",
  2         DECODE(GROUPING(JOB)+GROUPING(DEPTNO),1,'--SUBTOTAL--',2,NULL,JOB) "AVG_JOB",
  3         AVG(SAL)
  4    FROM EMP E
  5   GROUP BY ROLLUP(DEPTNO,JOB)
  6    ;

AVG_DEPTNO                               AVG_JOB        AVG(SAL)
---------------------------------------- ------------ ----------
10                                       CLERK              1300
10                                       MANAGER            2450
10                                       PRESIDENT          5000
10                                       --SUBTOTAL-- 2916.66666
20                                       CLERK               950
20                                       ANALYST            3000
20                                       MANAGER            2975
20                                       --SUBTOTAL--       2175
30                                       CLERK               950
30                                       MANAGER            2850
30                                       SALESMAN           1400
30                                       --SUBTOTAL-- 1566.66666
--TOTAL--                                             2073.21428

13 rows selected

-- 每个部门的平均工资 
SQL> SELECT DEPTNO,
  2         AVG(SAL)
  3    FROM EMP E
  4   GROUP BY DEPTNO;

DEPTNO   AVG(SAL)
------ ----------
    10 2916.66666
    20       2175
    30 1566.66666

-- 每个岗位的平均工资
SQL> SELECT JOB,
  2         AVG(SAL)
  3    FROM EMP E
  4   GROUP BY JOB;

JOB         AVG(SAL)
--------- ----------
ANALYST         3000
CLERK         1037.5
MANAGER   2758.33333
PRESIDENT       5000
SALESMAN        1400

SQL>