Re: GROUP BY

On Sep 7, 2:55 pm, brenda.belt..._at_gmail.com wrote:
> Hi everybody!
>
> Hope someone can help me with his views about the following issue, i
> will highly appreciate it!
>
> I am trying to query a column that is not in my GROUP BY clause,
>
> SELECT s.store_key,
> r.region_key,
> s.manag_key,
> SUM(sales),
> SUM(cost)
> FROM STORE
>
> WHERE
>
> So, it's obviously complaining. But I need this column, in the result
> set, is there any other way to have this very same column in my
> datagrid without including it in the

Providing an Oracle version (all four numbers) would be very helpful, as would the entire text of your query. 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:14:09 CDT