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.

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
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On 26.04.2007 16:31, DA Morgan wrote:
> 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/j...61-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...

On 26 Apr., 16:31, DA Morgan <damor...@psoug.org> wrote:
> 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.

> 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

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

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
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org