CODEWITHSUNDEEP
sqloracle-database
spyr0
spyr0

Reputation: 199

Resolve ORA-00937: not a single-group group function on summing rows

I have two tables. In Table A I have:

  | A_ID | DESCRIP  | 
  +------+----------+
  |   1  |  Thing1  |
  |   2  |  Thing2  |
  |   3  |  Thing3  |
  | .... |   .....  |

And in Table B I have:

  | A_ID | Amount  | 
  +------+----------+
  |   1  |   5.5    |
  |   1  |   8.2    |
  |   1  |  10.5    |
  |   2  |   7.4    |
  |   2  |   1.7    |
  |   2  |  15.9    |
  |  ... |    ....  |

And I want to join the SUM() of the AMOUNT rows in Table B where it's A_ID matches the A_ID in Table A.

My SQL is currently like:

SELECT 
A.A_ID,
A.DESCRIP,
SUM(B.AMOUNT)
FROM 
A

LEFT JOIN
 B
ON
 A.A_ID = B.A_ID
ORDER BY ARINVT.ID

This issue has shown up many times on this site I've seen. I haven't been able to find one that quite helps my situation, but in the issue link below:

This issue here probably helps my problem

I know I probably have to use analytics to resolve my problem. I have tried following some Youtube videos to do this but can't quite grasp. If someone could explain the accepted answer a bit more in the linked question it would be greatly appreciated!

EDIT: Added the actual sql code being used. Was simplifying when I thought I would need analytics

Current SQL:

SELECT 
ARINVT.ID,
ARINVT.CLASS,
ARINVT.ITEMNO,
ARINVT.DESCRIP,
ARINVT.ONHAND,
SUM(DAY_USE_TOTAL.TOT_MAT_QTY)

FROM 
IQMS.ARINVT ARINVT

LEFT JOIN
IQMS.DAY_USE_TOTAL DAY_USE_TOTAL
ON
ARINVT.ID = DAY_USE_TOTAL.ARINVT_ID

WHERE CLASS = 'PL'

GROUP BY ARINVT.ID,ARINVT.DESCRIP

The error I'm getting using GROUP BY is: ORA-00979: not a GROUP BY expression

Upvotes: 0

Views: 56

Answers (1)

hkutluay
hkutluay

Reputation: 6944

You have to use group by for non aggregated columns.

 SELECT ARINVT.ID,
     ARINVT.CLASS,
     ARINVT.ITEMNO,
     ARINVT.DESCRIP,
     ARINVT.ONHAND,
     SUM (DAY_USE_TOTAL.TOT_MAT_QTY)
FROM IQMS.ARINVT ARINVT
     LEFT JOIN IQMS.DAY_USE_TOTAL DAY_USE_TOTAL
        ON ARINVT.ID = DAY_USE_TOTAL.ARINVT_ID
   WHERE CLASS = 'PL'
GROUP BY ARINVT.ID,
         ARINVT.CLASS,
         ARINVT.ITEMNO,
         ARINVT.DESCRIP,
         ARINVT.ONHAND

Upvotes: 1

Related Questions