CODEWITHSUNDEEP
sqloracle-databaseora-00979
Stefan K
Stefan K

Reputation: 57

Resolve ORA-00979: not a group by expression

Maybe this is an easy one for some smart one to answer, I think the statemant is simple but not for me... The original statement uses a MAX for finding correct row but now there is a problem and I need the MIN as the aggregator to find the correct row. But then the problem with CASE togheter with GROUP BY and I get the ORA-00979: not a group by expression

I have searched and tried to follow other people with the same error and the suggested solutions but I dont get it to work due to a lot of trail and error. The statement is a small portion of the SQL

This statement works and get me the row(single column) with MAX

SELECT
    TRANSMAIN.TRANSEX AS TRANSEX,
    MAX(TRANSLOG.TRANSIK)AS TRANSIK
FROM
    tmsdat.TRANSLOG TRANSLOG,
    tmsdat.TRANSMAIN TRANSMAIN,
    tmsdat.TRCBUS TRCBUS
WHERE
    TRANSMAIN.TRCBUSNO=TRCBUS.TRCBUSNO
    AND TRANSMAIN.TRANSIK >=5246867 
    AND TRANSMAIN.TRANSIK=TRANSLOG.TRANSIK

GROUP
    BY TRANSMAIN.TRANSEX

But this modified statement gets the error I understand it is NOT possible to use the CASE in there but just to explain what needs to be done.

SELECT
    TRANSMAIN.TRANSEX AS TRANSEX,
    (case when  TRANSMAIN.TRCBUSNO = 1282
    MIN(TRANSLOG.TRANSIK) else
    MAX(TRANSLOG.TRANSIK) end ) AS TRANSIK
FROM
    tmsdat.TRANSLOG TRANSLOG,
    tmsdat.TRANSMAIN TRANSMAIN,
    tmsdat.TRCBUS TRCBUS
WHERE
    TRANSMAIN.TRCBUSNO=TRCBUS.TRCBUSNO
    AND TRANSMAIN.TRANSIK >=5246867 
    AND TRANSMAIN.TRANSIK=TRANSLOG.TRANSIK

GROUP
    BY TRANSMAIN.TRANSEX

Hope someone sees an easy solution. Regards /Stefan

Upvotes: 0

Views: 191

Answers (1)

MT0
MT0

Reputation: 167962

Either include TRCBUSNO in the GROUP BY clause:

SELECT tm.TRANSEX AS TRANSEX,
       CASE
       WHEN tm.TRCBUSNO = 1282
       THEN MIN(tl.TRANSIK)
       ELSE MAX(tl.TRANSIK)
       END AS TRANSIK
FROM   tmsdat.TRANSLOG tl
       INNER JOIN tmsdat.TRANSMAIN tm
       ON (tm.TRANSIK=tl.TRANSIK)
       INNER JOIN tmsdat.TRCBUS tb
       ON (tm.TRCBUSNO=tb.TRCBUSNO)
WHERE  tm.TRANSIK >=5246867 
GROUP BY
       tm.TRANSEX,
       tm.trcbusno

Or else aggregate by that column:

SELECT tm.TRANSEX AS TRANSEX,
       CASE
       WHEN MIN(tm.TRCBUSNO) = 1282
       THEN MIN(tl.TRANSIK)
       ELSE MAX(tl.TRANSIK)
       END AS TRANSIK
FROM   tmsdat.TRANSLOG tl
       INNER JOIN tmsdat.TRANSMAIN tm
       ON (tm.TRANSIK=tl.TRANSIK)
       INNER JOIN tmsdat.TRCBUS tb
       ON (tm.TRCBUSNO=tb.TRCBUSNO)
WHERE  tm.TRANSIK >=5246867 
GROUP BY
       tm.TRANSEX

or:

SELECT tm.TRANSEX AS TRANSEX,
       CASE
       WHEN COUNT(CASE WHEN tm.TRCBUSNO = 1282 THEN 1 END) > 0
       THEN MIN(tl.TRANSIK)
       ELSE MAX(tl.TRANSIK)
       END AS TRANSIK
FROM   tmsdat.TRANSLOG tl
       INNER JOIN tmsdat.TRANSMAIN tm
       ON (tm.TRANSIK=tl.TRANSIK)
       INNER JOIN tmsdat.TRCBUS tb
       ON (tm.TRCBUSNO=tb.TRCBUSNO)
WHERE  tm.TRANSIK >=5246867 
GROUP BY
       tm.TRANSEX

Upvotes: 0

Related Questions