CODEWITHSUNDEEP
sqlsumaverage
mmccl267
mmccl267

Reputation: 63

SQL Average of a Sum

I'm trying to calculate the average availability by work center, to do so I'm calculating # of production hours on the machine divided by the number of hours in a shift. The problem is when multiple jobs run on a machine in one shift, I need to sum all those production hours and divide by 8 before getting the average.

I hope this makes sense...here's my current SQL code which is returning "ORA-00937: not a single-group group function"

Select WORK_CENTER.EQNO,
AVG((Sum(V_COMB_PROD_CRW1.PROD_HRS) / 8 * 100)) As Available
From WORK_CENTER
Left Join V_COMB_PROD_CRW1 On WORK_CENTER.EQNO = V_COMB_PROD_CRW1.EQNO
Group By WORK_CENTER.EQNO

Upvotes: 0

Views: 429

Answers (1)

zip
zip

Reputation: 4061

The problem here is that you are doing an aggregate of an aggregate, and the engine wont accept it. You need to find the identifier of the shifts (I called it shiftID_Column). Get sum/8 per shift and do the average of those by EQNO Instead do:

select EQNO, avg(Available_Pershift) as Available
from
(
  Select shiftID_Column, WORK_CENTER.EQNO,
 (Sum(V_COMB_PROD_CRW1.PROD_HRS) / 8 * 100)  As Available_Pershift
  From WORK_CENTER
  Left Join V_COMB_PROD_CRW1 On WORK_CENTER.EQNO = V_COMB_PROD_CRW1.EQNO
  Group By shiftID_Column, WORK_CENTER.EQNO
) group by EQNO

Upvotes: 1

Related Questions