Re: Group By Questions..

From: Jeff Hunter <jeffh_at_btitelecom.net>
Date: Fri, 1 Oct 1999 16:31:33 -0400
Message-ID: <37f51a6b@defiant.btitelecom.net>

Appearantly, not.

SQL> create table jh (b number(3), c number(3), d number(3));

Table created.

SQL>
SQL> drop table jh;

Table dropped.

SQL> create table jh (a number(3), b number(3), c number(3), d number(3));

Table created.

SQL> insert into jh values (1, 2, 3, 4);

1 row created.

SQL> insert into jh values (4, 2, 3, 4);

1 row created.

SQL> insert into jh values (3, 4, 3, 2);

1 row created.

SQL> insert into jh values (1, 4, 3, 2);

1 row created.

SQL> commit;

Commit complete.

SQL> select b,c,d, avg(a) group by b,c,d; select b,c,d, avg(a) group by b,c,d

                     *

ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> select b,c,d, avg(a) from jh group by b,c,d;

         B C D AVG(A) ---------- ---------- ---------- ----------

         2          3          4        2.5
         4          3          2          2

SQL> select b,c,d, avg(a) from jh group by b,d,c;

         B C D AVG(A) ---------- ---------- ---------- ----------

         2          3          4        2.5
         4          3          2          2

SQL> select b,c,d, avg(a) from jh group by c,b,d;

         B C D AVG(A) ---------- ---------- ---------- ----------

         2          3          4        2.5
         4          3          2          2

SQL>
Jimmy wrote in message <37F5DB19.5363BFB9_at_comp.polyu.edu.hk>...
>Hello all,
>
> Assume I have a table with column A, B, C and D.
>
> I wonder the following SQL statements will produce the same results:
>
> i) select B, C, D, avg(A) from AAA group by B, C, D;
> ii) select B, C, D, avg(A) from AAA group by B, D, C;
> iii) select B, C, D, avg(A) from AAA group by C, B, D;
> ....
>
> i.e. does the order of group by clause affect the final result (or
>performance, if any) of the SQL statement? Anyone knows white papers or
>documents discussing this issue?
>
>Thanks,
>Jimmy
>
>
>
>
Received on Fri Oct 01 1999 - 15:31:33 CDT