Re: GROUP BY clause

On Sep 7, 3:14 pm, 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.
>
> Thanks very much for your suggestions!

Providing an Oracle version (all four numbers) would be very helpful. Generally speaking you can't include any non-aggregate columns in a dataset which aren't in the group by clause, however you might be able to rewrite the query minus
the 'offending' column and make it an in-line view, then 'tack on' the required column in a join. Since that shouldn't alter your summed results (you're not intending to include the column in the group by) you might get away with it. As an example:

SQL> select d.dname, e.deptno, sum(e.sal)   2 from emp e, dept d
  3 where d.deptno = e.deptno
  4 group by e.deptno;
select d.dname, e.deptno, sum(e.sal)

       *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

SQL> select d.dname, x.deptno, x.sumsal
  2 from dept d, (select deptno, sum(sal) sumsal from emp group by deptno) x
  3 where d.deptno = x.deptno
  4 /

DNAME DEPTNO SUMSAL
-------------- ---------- ----------

RESEARCH               20      10875
ACCOUNTING             10       8750
SALES                  30       9400

SQL> You might give it a try.

David Fitzjarrell Received on Fri Sep 07 2007 - 15:22:48 CDT