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

Answer 1

Hello. 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

Answer 2

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)

Answer 3

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]