CODEWITHSUNDEEP
sqloracle-databaseplsqlsubquery
Jeff Guttry
Jeff Guttry

Reputation: 109

Modify Select Statement To Sum Single Field

I have the below select statement that has extracted all the data I need, but I am trying to modify it so that the REJECTS by SHIFT by PROD_DATE are summed. 

SELECT B.PROD_DATE,B.SHIFT,B.REJECTS
FROM REJECTS B 
LEFT OUTER JOIN HIST_ILLUM_PART C ON B.HIST_ILLUM_PART_ID = C.ID
LEFT OUTER JOIN HIST_ILLUM_RT A ON A.ID = C.HIST_ILLUM_RT_ID
WHERE
B. REJECT_CODE NOT in ('START','SETUP','QC')
AND B.PROD_DATE >= SYSDATE - 8
ORDER BY SHIFT, PROD_DATE

I have tried 

   SELECT B.PROD_DATE,B.SHIFT,SUM(B.REJECTS)

I recieve the following error: ORA-00937: not a single-group group function

Do I need a subquery?

Upvotes: 0

Views: 43

Answers (1)

PausePause
PausePause

Reputation: 806

Add a GROUP BY clause to your query when doing aggregations. In pseudocode it means for each distinct group of B.PROD_DATE and B.SHIFT get the sum of all B.REJECTS for that set, 

SELECT B.PROD_DATE,B.SHIFT,SUM(B.REJECTS) AS REJECTS
FROM REJECTS B 
LEFT OUTER JOIN HIST_ILLUM_PART C ON B.HIST_ILLUM_PART_ID = C.ID
LEFT OUTER JOIN HIST_ILLUM_RT A ON A.ID = C.HIST_ILLUM_RT_ID
WHERE
B. REJECT_CODE NOT in ('START','SETUP','QC')
AND B.PROD_DATE >= SYSDATE - 8
GROUP BY B.PROD_DATE,B.SHIFT
ORDER BY SHIFT, PROD_DATE

Upvotes: 2

Related Questions