Re: Group By Questions..

Another angle to this

On Oracle 815, use the CUBE function in tandem with GROUP BY. This will give you sub-totals across all combinations of your GROUP BY clause, and also provide aggregates across the columns that are specified.

However, it might give you more information than you need!

The order of columns in a GROUP BY clause is important as it affects the interpretation of the result set.
Regards,
VJ

Frank Hubeny <fhubeny_at_ntsource.com> wrote in message news:37F60875.BB49A27_at_ntsource.com...
> Connor McDonald mentioned that the data returned, ignoring order, would
not
> be different and it occurred to me that perhaps the original question
> assumed that a different ordering does not imply a different result.
>
> Intuitively, at least, a group by clause partitions the three columns,
> viewed as triplets, into subsets where each element of the set has their
> first, second and third components equal. If this is the case, then as
long
> as the columns are not null the results should be identical. This seems
to
> be what Tom Verhagen was referring to when he claimed that there would be
no
> difference if the three columns were part of the primary key.
>
> However, if the columns are permitted to be null, can a different grouping
> arise?
>
> If the nulls are consistently considered either equal or not equal, then
the
> results should still be the same, ignoring order. Oracle appears to
> consider null to equal null when constructing the groups as shown below:
>
> The following table contains nulls in one, two or all three columns:
>
> SQL> select * from jh;
>
> A B C D
> --------- --------- --------- ---------
> 1 2 2
> 1 2 2
> 1 2
> 1 2
> 1
> 1
>
> If a null is equal to a null in forming the groups then there should be
> three rows returned. If nulls are not equal to nulls then each of the rows
> in jh should be in their own group.
>
> SQL> select b,c,d, avg(a) from jh group by c,b,d;
>
> B C D AVG(A)
> --------- --------- --------- ---------
> 2 2 1
> 2 1
> 1
>
> As the query shows, a null is considered equal to a null in constructing
the
> groups, so the results, ignoring order, will be identical.
>
> Frank Hubeny
>
>
>
> Jimmy wrote:
>
> > 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 Sun Oct 03 1999 - 03:14:14 CDT