| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: group by clause ordering
> Marie, no, it is not possible to get other resulting rows by only
> changing the group by order. You get the same groupings, no matter if
> you group by a first and then by b or vice versa. And as all
> aggregation (min, max, count, ...) is done per group, you definitely
> get the same result rows.
Yea. But things start to differ of course if you are using for example
rollup, because then subsums are calculated for group by clause
starting from right to left.
SQL> desc employees
Name Null? Type ----------------------------------------- -------- ------------------ EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)SQL> ed
1 select sum(salary), department_id, job_id
2 from employees
3* group by rollup (department_id, job_id)
SQL>
SQL> /
SUM(SALARY) DEPARTMENT_ID JOB_ID
----------- ------------- ----------
7000 SA_REP
7000
4400 10 AD_ASST
4400 10
13000 20 MK_MAN
6000 20 MK_REP
19000 20
5800 50 ST_MAN
11700 50 ST_CLERK
17500 50
19200 60 IT_PROG
19200 60
10500 80 SA_MAN
19600 80 SA_REP
30100 80
34000 90 AD_VP
24000 90 AD_PRES
58000 90
12000 110 AC_MGR
8300 110 AC_ACCOUNT
20300 110
175500
22 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select sum(salary), department_id, job_id
2 from employees
3* group by rollup (job_id, department_id)
SQL> /
SUM(SALARY) DEPARTMENT_ID JOB_ID
----------- ------------- ----------
34000 90 AD_VP
34000 AD_VP
12000 110 AC_MGR
12000 AC_MGR
13000 20 MK_MAN
13000 MK_MAN
6000 20 MK_REP
6000 MK_REP
10500 80 SA_MAN
10500 SA_MAN
7000 SA_REP
19600 80 SA_REP
26600 SA_REP
5800 50 ST_MAN
5800 ST_MAN
4400 10 AD_ASST
4400 AD_ASST
24000 90 AD_PRES
24000 AD_PRES
19200 60 IT_PROG
19200 IT_PROG
11700 50 ST_CLERK
11700 ST_CLERK
8300 110 AC_ACCOUNT
8300 AC_ACCOUNT
175500
26 rows selected.
SQL>
Using cube things are back to normal (result set doesn't differ,
although ordering without explicit order by clause of course might
differ). Actually rollup is a subset of cube :)
SQL> ed
Wrote file afiedt.buf
1 select sum(salary), department_id, job_id
2 from employees
3* group by cube (job_id, department_id)
SQL> /
SUM(SALARY) DEPARTMENT_ID JOB_ID
----------- ------------- ----------
7000
175500
4400 10
19000 20
17500 50
19200 60
30100 80
58000 90
20300 110
34000 AD_VP
34000 90 AD_VP
12000 AC_MGR
12000 110 AC_MGR
13000 MK_MAN
13000 20 MK_MAN
6000 MK_REP
6000 20 MK_REP
10500 SA_MAN
10500 80 SA_MAN
7000 SA_REP
26600 SA_REP
19600 80 SA_REP
5800 ST_MAN
5800 50 ST_MAN
4400 AD_ASST
4400 10 AD_ASST
24000 AD_PRES
24000 90 AD_PRES
19200 IT_PROG
19200 60 IT_PROG
11700 ST_CLERK
11700 50 ST_CLERK
8300 AC_ACCOUNT
8300 110 AC_ACCOUNT
34 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select sum(salary), department_id, job_id
2 from employees
3* group by cube (department_id, job_id)
SQL> /
SUM(SALARY) DEPARTMENT_ID JOB_ID
----------- ------------- ----------
7000
175500
34000 AD_VP
12000 AC_MGR
13000 MK_MAN
6000 MK_REP
10500 SA_MAN
7000 SA_REP
26600 SA_REP
5800 ST_MAN
4400 AD_ASST
24000 AD_PRES
19200 IT_PROG
11700 ST_CLERK
8300 AC_ACCOUNT
4400 10
4400 10 AD_ASST
19000 20
13000 20 MK_MAN
6000 20 MK_REP
17500 50
5800 50 ST_MAN
11700 50 ST_CLERK
19200 60
19200 60 IT_PROG
30100 80
10500 80 SA_MAN
19600 80 SA_REP
58000 90
34000 90 AD_VP
24000 90 AD_PRES
20300 110
12000 110 AC_MGR
8300 110 AC_ACCOUNT
34 rows selected.
SQL> More advanced constructions and explanations in in datawarehousing guide.
Gints Plivna
http://www.gplivna.eu
Received on Fri Apr 27 2007 - 04:32:49 CDT
 |
 |