Re: GROUP BY clause

On Sep 9, 12:36 pm, Jan Krueger <j..._at_stud.uni-hannover.de> wrote:
> brenda.belt..._at_gmail.com wrote:
> > Hi everybody,
>
> > I just got to this area, so hope someone can help me with the
> > following issue, I will really appreciate it.
>
> > I want to query a column that is not part of the GROUP BY clause:
>
> > SELECT c.country_id,
> > r.region_id,
> > r.manag_id,
> > SUM(r.sls),
> > SUM(r.cst)
> > FROM country c INNER JOIN region r ON c.country_key = r.country_key
> > GROUP BY c.country_id,
> > r.region_id;
>
> > Obviously, it's complaining because r.manag_id is not in the GROUP
> > BY clause, but I need the result set to be as defined in the column
> > clause. I wonder if there is any way to have these same columns in my
> > query keeping my GROUP BY clause as it is now.
>
> Hi Brenda,
>
> maybe I didn't get the point here, but I can't see the benefit of the
> inline view suggestions.
> If you just add the r.manag_id as a third column to your group by
> clause, why is the resultset different from the one you expect?
>
> Jan- Hide quoted text -
>
> - Show quoted text -

Because the aggregate values won't be the same as the grouping has changed:

SQL> create table grouptest(id number, deptno number, groupno number, salary number);

Table created.

SQL>
SQL> insert all
  2 into grouptest
  3 values
  4 (1,10,1,4000)
  5 into grouptest
  6 values
  7 (2,20,1,4000)
  8 into grouptest
  9 values
 10 (3,10,2,4000)
 11 into grouptest
 12 values
 13 (4,10,3,4000)
 14 into grouptest
 15 values
 16 (5,30,1,4000)
 17 into grouptest
 18 values
 19 (6,30,2,4000)
 20 into grouptest
 21 values
 22 (7,40,1,4000)
 23 into grouptest
 24 values
 25 (8,40,5,4000)
 26 into grouptest
 27 values
 28 (9,20,1,4000)
 29 into grouptest
 30 values
 31 (10,30,7,4000)
 32 into grouptest
 33 values
 34 (11,60,4,4000)
 35 into grouptest
 36 values
 37 (12,70,8,4000)
 38 into grouptest
 39 values
 40 (13,30,9,4000)
 41 into grouptest
 42 values
 43 (14,20,4,4000)
 44 into grouptest
 45 values
 46 (15,10,8,4000)
 47 select * from dual;

15 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select deptno, groupno, sum(salary)   2 from grouptest
  3 group by deptno, groupno;

    DEPTNO GROUPNO
SUM(SALARY)
---------- ----------

        20 1
8000

        40 1
4000

        30 7
4000

        60 4
4000

        10 3
4000

        40 5
4000

        30 1
4000

        20 4
4000

        10 1
4000

        70 8
4000

        30 2
4000

    DEPTNO GROUPNO
SUM(SALARY)
---------- ----------

        30 9
4000

        10 8
4000

        10 2
4000

14 rows selected.

SQL>
SQL> select g.deptno, x.groupno, x.salsum   2 from grouptest g,
  3 (select groupno, sum(salary) salsum   4 from grouptest
  5 group by groupno) x
  6 where g.groupno = x.groupno;

    DEPTNO GROUPNO
SALSUM
---------- ----------

        30 7
4000

        40 5
4000

        10 8
8000

        70 8
8000

        20 1
20000

        40 1
20000

        30 1
20000

        20 1
20000

        10 1
20000

        30 2
8000

        10 2
8000

    DEPTNO GROUPNO
SALSUM
---------- ----------

        10 3
4000

        20 4
8000

        60 4
8000

        30 9
4000

15 rows selected.

SQL>
SQL> select x.deptno, g.groupno, x.salsum   2 from grouptest g,
  3 (select deptno, sum(salary) salsum
  4 from grouptest
  5 group by deptno) x
  6 where g.deptno = x.deptno;

    DEPTNO GROUPNO
SALSUM
---------- ----------

        70 8
4000

        60 4
4000

        30 9
16000

        30 7
16000

        30 2
16000

        30 1
16000

        40 5
8000

        40 1
8000

        20 4
12000

        20 1
12000

        20 1
12000

    DEPTNO GROUPNO
SALSUM
---------- ----------

        10 8
16000

        10 3
16000

        10 2
16000

        10 1
16000

15 rows selected.

SQL> David Fitzjarrell Received on Mon Sep 10 2007 - 08:26:34 CDT