Oracle Database :: group by clause ordering


	group by clause ordering Is there any way in which select <blar> from <blar> group by a,b would not give the same results as same query with the columns in the group by clause changed select <blar> from <blar> group by b,a Ignoring ordering. I am thinking about null columns, duplicate rows min max, count functions here. - Hide quoted text - mariegriffiths wrote: > Is there any way in which > select <blar> > from <blar> > group by a,b > would not give the same results as same query with the columns in the > group by clause changed > select <blar> > from <blar> > group by b,a > Ignoring ordering. > I am thinking about null columns, duplicate rows min max, count > functions here. <blar>? Write real SQL and perhaps we can help you. -- Daniel A. Morgan University of Washington damor@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org -----------------------------------------------Reply----------------------------------------------- On 26.04.2007 16:31, DA Morgan wrote: - Hide quoted text - > mariegriffiths wrote: >> Is there any way in which >> select <blar> >> from <blar> >> group by a,b >> would not give the same results as same query with the columns in the >> group by clause changed >> select <blar> >> from <blar> >> group by b,a >> Ignoring ordering. >> I am thinking about null columns, duplicate rows min max, count >> functions here. > <blar>? http://www.blackwellpublishing.com/journal.asp?ref=0261-3050&site=1 > Write real SQL and perhaps we can help you. Real SQL for real DBA's. :-) SCNR robert PS: It's so warm over here that my brain probably starts frying... -----------------------------------------------Reply----------------------------------------------- On 26 Apr., 16:31, DA Morgan <damor@psoug.org> wrote: - Hide quoted text - > mariegriffiths wrote: > > Is there any way in which > > select <blar> > > from <blar> > > group by a,b > > would not give the same results as same query with the columns in the > > group by clause changed > > select <blar> > > from <blar> > > group by b,a > > Ignoring ordering. > > I am thinking about null columns, duplicate rows min max, count > > functions here. > <blar>? > Write real SQL and perhaps we can help you. > -- > Daniel A. Morgan I don't agree. Marie is asking if it is possible to get other result rows only by changing the order of a group by clause, so she puts in <blar> to show that it can be anything. Use your imagination. Is there any case you can think of that only the change of order in group by leads to other result rows? 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. -----------------------------------------------Reply----------------------------------------------- > 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 Wrote file afiedt.buf 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 -----------------------------------------------Reply----------------------------------------------- On 27 Apr., 11:32, Gints Plivna <gints.pli@gmail.com> wrote: > > 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. Good point. Rollup had totally slipped my mind -----------------------------------------------Reply----------------------------------------------- Thorsten Kettner wrote: > On 27 Apr., 11:32, Gints Plivna <gints.pli@gmail.com> wrote: >>> 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. > Good point. Rollup had totally slipped my mind Not mine which is why I didn't want to answer without seeing a real SQL statement. Among the variations are: CUBE GROUP_ID GROUPING GROUPING SETS ROLLUP -- Daniel A. Morgan University of Washington damor@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org

MS Dynamics CRM 3.0

Oracle Database

group by clause ordering