ORA-00979: not a GROUP BY expression?

Question

I have found the solution to this, but what in case one of the column is a subquery, how can i include it in group by, or do i need to include that in group by. I will paste the query here..

SELECT s.customerid, s.denomid,
       (SELECT su.quantity
         FROM   stockupdations su
         WHERE  s.customerid = su.custid
         AND    s.denomid = su.denomid
         AND    s.curid = su.curid) AS cur_stock, c.name AS cus_name, d.denomname AS denom,
       cur.curcode AS currency
FROM   stock s
LEFT   JOIN customers c
ON     s.customerid = c.custid
LEFT   JOIN denomination d
ON     d.denomid = s.denomid
LEFT   JOIN currency cur
ON     cur.curid = s.curid
GROUP  BY s.denomid, s.customerid, c.name, d.denomname, cur.curcode
ORDER  BY s.customerid ASC

Why are you using `GROUP BY` when you are not using any aggregate functions (such as `SUM`, `COUNT`)? What are you trying to achieve? Please post input/expected output. — shahkalpesh, Oct 13 '10 at 19:24
@shahkalpesh: The result is the same as using `DISTINCT`, just more typing - See the [AskTom for more info](http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:32961403234212). Having a GROUP BY clause doesn't require using aggregate functions. — OMG Ponies, Mar 11 '11 at 03:50

Benoit · Answer 1 · 2010-10-13T07:13:30.553

What about a WITH statement?

WITH tmp AS
(
    SELECT s.customerid, s.denomid,
           c.name AS cus_name,
           d.denomname AS denom,
           cur.curcode AS currency
    FROM   stock s
    LEFT   JOIN customers c
    ON     s.customerid = c.custid
    LEFT   JOIN denomination d
    ON     d.denomid = s.denomid
    LEFT   JOIN currency cur
    ON     cur.curid = s.curid
    GROUP  BY s.denomid, s.customerid, c.name, d.denomname, cur.curcode
    ORDER  BY s.customerid ASC
)
SELECT tmp.customerid, tmp.denomid,
       su.quantity,
       tmp.cus_name,
       tmp.denom,
       tmp.currency
FROM   tmp
INNER  JOIN stockupdations su
ON     tmp.customerid = su.custid
AND    tmp.denomid = su.denomid
AND    tmp.curid = su.curid

A subselect can not be changed into an INNER JOIN, because the subselect will return null if there's no records in the `STOCKUPDATIONS` table based on the criteria. That means you need to use an OUTER join. — OMG Ponies, Mar 11 '11 at 04:05

score 0 · Answer 2 · answered Oct 13 '10 at 07:18

0

You can use your "Inner query" in the from clause than on the select.

Say I have a CUSTOMER table and ORDER table,

I can have something like

SELECT C.CUSTOMER_ID, COUNT(T.ORDER_ID)
FROM CUSTOMERS C
JOIN (SELECT CUSTOMER_ID, ORDER_ID, ORDER_DATE, ORDER_STATUS FROM ORDERS O WHERE O.STATUS <> 'DECLINED') T
ON T.CUSTOMER_ID = C.CUSTOMER ID
GROUP BY C.CUSTOMER_ID

(This SQL is just an example, and I know there are better ways to write this, but I could not think of any other example immediately)

answered Oct 13 '10 at 07:18

Nivas

18,126
4
62
76

A subselect can not be changed into an INNER JOIN, because the subselect will return null if there's no records in the `STOCKUPDATIONS` table based on the criteria. That means you need to use an OUTER join. – OMG Ponies Mar 11 '11 at 04:04

score 0 · Answer 3 · answered Oct 14 '10 at 03:33

You don't have to do everything at once. Try breaking your query into multiple pieces. Subqueries, analytic functions, or other complicated logic will look like simple rows to the outer query. (Don't worry about performance, Oracle will re-write it and do everything as one step if it makes sense.)

--Step 3
select [simple values]
from
(
  --Step 2
  select [insanity]
  from
  (
    --Step 1
    select [madness]
    from
    [impossible joins]
  )
)
group by [simple values]

ORA-00979: not a GROUP BY expression?

3 Answers3